Apply formula with VBA using dynamic cell references.

1
votes

Hi I am trying to apply a formula to a range of cells based on a condition. However if I loop through the cells applying the formula then it applies it without dynamic cell referencing. (like as if I'd try to drag the formula down manually by clicking on the bottom right corner of the initial cell). How would I go about making the cell references in the applied formula dynamic? Any help would be appreciated.

Here's my code:

Sub OQWDays()
Dim oqs As Worksheet

Set oqs = Sheets("SQL_IMPORT")

    For x = 2 To oqs.Cells(Rows.Count, "A").End(xlUp).Row

        If oqs.Range("J" & x).Value = ("CBN_Suisse") Then oqs.Range("A" & x).Formula = "=NETWORKDAYS(D2,PUBLIC_HOLIDAYS!$G$3,PUBLIC_HOLIDAYS!$E$44:$E$61)"

    Next x
End Sub
1
vbaexcelloopsexcel-formula
so you want D2 to change to Dx where x is the row number? - Scott Craner
replace D2, with D" & x & ", - Scott Craner

1 Answers

2
votes

I think you mean this. Take the 2 out of the string and replace with x?

Sub OQWDays()

Dim oqs As Worksheet

Set oqs = Sheets("SQL_IMPORT")

For x = 2 To oqs.Cells(Rows.Count, "A").End(xlUp).Row
    If oqs.Range("J" & x).Value = "CBN_Suisse" Then
        oqs.Range("A" & x).Formula = "=NETWORKDAYS(D" & x & ",PUBLIC_HOLIDAYS!$G$3,PUBLIC_HOLIDAYS!$E$44:$E$61)"
    End If
Next x

End Sub