CODEWITHSUNDEEP
vbaexcel
D. Todor
D. Todor

Reputation: 157

Excel VBA - Adjusting a formula to let VBA loop it

I have following formula

=IF(Matching_Sheet!F3=0;"";Matching_Sheet!F3)

I want to let VBA fill in the B column of a sheet with this formula adjusted to the respective column number. I have tried this

For x = 2 To destMaxRow
Range("$B$" & x).FormulaLocal = "=IF(Matching_Sheet!$F$=0;" & x & Chr(34) & Chr(34) & ";Matching_Sheet!$F$)" & x
Next

Unfortunately, this form apparently has a mistake but I cannot figure out what it is.

Any help would be appreciated. Thanks in advance.

Upvotes: 0

Views: 49

Answers (1)

SJR
SJR

Reputation: 23081

Not sure, try this

For x = 2 To destMaxRow
  Range("$B$" & x).FormulaLocal = "=IF(Matching_Sheet!$F$" & x & "=0;" & Chr(34) & Chr(34) & ";Matching_Sheet!$F$" & x & ")"
Next

Which I think could be shortened to (obviating need for a loop)

Range("$B$2:B" & destMaxRow).FormulaLocal = "=IF(Matching_Sheet!$F2=0;" & Chr(34) & Chr(34) & ";Matching_Sheet!$F2)"

Upvotes: 1

Related Questions