Replicating Cell formula in Excel through VBA

Question

I want to create the IF formula in column C within VBA. However, when I do it, the formula comes out as using values of the cells rather than the cell reference. I want to know how to use cell reference:

[Have the formula say [=IF(B2>0,2,A2)] rather than [=IF(1>0,2,1] in my excel sheet for cell C2 when the VBA is ran.]

Dim c As Range
Dim a As Integer
Dim b As Integer

'//loop it in column C
For Each c In Range(Range("C2"), Range("C2").End(xlDown))
    a = c.Offset(0, -1) '// Column B
    b = c.Offset(0, -2) '// Column A
    c.Formula = "=IF(" & b & ">0,2," & a & ")" '// same format as formula [=IF(B2>0,2,A2)]
   
Next

BigBen BigBen · Accepted Answer · 2020-12-19T13:35:56

If I understand correctly:

Dim a As Range
Dim b As Range

Set b = c.Offset(0, -1) '// Column B
Set a = c.Offset(0, -2) '// Column A

c.Formula = "=IF(" & b.Address(False,False) & ">0,2," & a.Address(False,False) & ")"

But you can accomplish this without looping or using the Address; Excel will update the relative references as the formula is filled down the column.

With ActiveSheet
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

    .Range("C2:C" & lastRow).Formula = "=IF(B2>0,2,A2)"
End With

Replicating Cell formula in Excel through VBA

1 Answers