none
Workday.Int formula through Macro or VBA RRS feed

  • Question

  • Hi,

    I have a excel sheet with many column and rows with the below repetitive format. I want to copy row j below row k delete row i and j Then change the formula in row j in its new position.

    I want the formula =workday.intl(Now(),(Sum($L$i,$L$j)/7.9),1)

    I want this formula to appear on the excel sheet as i will be changing the values of row i and row j everyday so please help.

    Sub trt()
    i = ActiveCell.Row
    j = i + 1
    k = j + 1
    l = k + 1
    Cells(j, 1).EntireRow.Copy
    Cells(l, 1).EntireRow.Select
    Selection.Insert Shift:=xlDown
    Cells(i, 1).EntireRow.Delete
    Cells(i, 1).EntireRow.Delete
    Cells(i, 13).ClearContents
    Cells(i, 14).Formula = =workday.intl(Now(),(Sum(Range(Cells(i,12),Cells(j,12))/7.9),1)
    Cells(i, 14).NumberFormat = "m/d/yy"
    End Sub

    Tuesday, March 1, 2016 5:14 PM

All replies

  •   Cells(i, 14).Formula = "=workday.intl(Now(),(Sum(" & Range(Cells(i, 12), Cells(j, 12)).Address & ")/7.9),1)"

    Tuesday, March 1, 2016 5:43 PM