VBA: Using a WORKDAY formula that references the cell above

[laurie9300]

I'm working on a scheduling spreadsheet that has evolved over time


The relevent data is a date in column 7 (G) & the build time in minutes in column 12 (L)


Part of the macro that runs on this sheet includes some code kindly provided by @JLGWhiz


The code divides the biuld time by 426 (1 workday), adds a new row for every full workday, and subtract 1 workday from the date on each consecutive row


Here's the code (I have removed irrelevant lines)

Dim rw As Long, v As Long, r As Integer, i As Long, wf As WorksheetFunction
Set wf = Application.WorksheetFunction
    For rw = .Cells(Rows.Count, 12).End(xlUp).Row To 2 Step -1
        If .Cells(rw, 12).Value >= 426 Then
            v = Cells(rw, 12).Value
            If v Mod 426 > 0 Then
                r = Int(v / 426)
                .Cells(rw, 12).Offset(1).Resize(r).EntireRow.Insert
                .Cells(rw, 12).Offset(1).Resize(r) = 426
                .Cells(rw, 12) = v Mod 426
                For i = 1 To r
                    
                    .Cells(rw, 12).Offset(i, -5) = wf.WorkDay(.Cells(rw, 12).Offset(, -5).Value, -i)
                    
                Next
            Else
                r = Int(v / 426)
                .Cells(rw, 12).Offset(1).Resize(r - 1).EntireRow.Insert
                .Cells(rw, 12).Offset(1).Resize(r - 1) = 426
                .Cells(rw, 12) = 426
                   For i = 1 To r
 	                  .Cells(rw, 12).Offset(i, -5) = wf.WorkDay(.Cells(rw, 12).Offset(, -5).Value, -i)
                    
                Next
            End If
        End If
    Next
End With

The column references have changed, but the thread is here: https://www.mrexcel.com/forum/excel...row-based-cell-value-modify-previous-row.html


This code has been awesome, but I'd like to add a tweak that would make re-scheduling much easier


Instead of this line.....

.Cells(rw, 12).Offset(i, -5) = wf.WorkDay(.Cells(rw, 12).Offset(, -5).Value, -i)

.....just pasting in the previous WORKDAY date, I would like it to put in a formula?


The formula needs to be equivilent to:


=WORKDAY("cell above",-1)


This would make the dates in the inserted rows dynamic, so if I move the date in the original date cell, the subsequnt dates follow automatically


After much googling, I have given up. I'm guessing I have to put each date cell reference into a variable and then insert it into the formula?


This is wat above my current skill level, any help achieving this would be most welcome......Laurie

 

[laurie9300]

Bump ....... nobody?

 

[Fazza]

just simply giving a suggestion without much reading/understanding,

.Cells(rw, 12).Offset(i, -5).FormulaR1C1 = "=WORKDAY(R[-1]C,-1)"

 

[laurie9300]

just simply giving a suggestion without much reading/understanding,

.Cells(rw, 12).Offset(i, -5).FormulaR1C1 = "=WORKDAY(R[-1]C,-1)"

Perfect! Thank you! Seems I was overthinking/overcomplicating the whole thing!

 

[Fazza]