CODEWITHSUNDEEP
vbaexcelloopsexcel-formula
Rhyfelwr
Rhyfelwr

Reputation: 329

Apply formula with VBA using dynamic cell references.

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

Upvotes: 1

Views: 933

Answers (1)

SJR
SJR

Reputation: 23081

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

Upvotes: 2

Related Questions