Excel VBA - Adjusting a formula to let VBA loop it

Question

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.

SJR · Accepted Answer · 2017-06-06 11:24:56Z

1

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)"

answered Jun 6, 2017 at 11:24

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - Adjusting a formula to let VBA loop it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related