I am attempting to place the following formula into a cell through VBA:
=IF('Other Sheet'!D2="", 'Other Sheet'!D1, 'Other Sheet'!D2)
I have replaced the row number with variables and am attempting to concatenate them together as the formula is repeated as part of a loop.
ActiveCell.Formula = "=IF('Other Sheet'!D" & Row2 "="""", 'Other Sheet'!D" & Row1 ", 'Other Sheet'!D" & Row2 ")"
The formula works without variables when pasted into a cell or when in a line as vba however when I attempt to split it up, I get either a syntax error or an expected end of statement error around the equal sign.
I am very new to VBA and only have about 2 weeks experience with it.
I have tried troubleshooting by breaking apart the if statement and changing the location of double quotes and adding the .Formula suffix but nothing seems to be working.
Ideally after concatenation the code would repeat for each loop increasing by 5
ie.
first pass =IF('Other Sheet'!D2="", 'Other Sheet'!D1, 'Other Sheet'!D2)
second pass =IF('Other Sheet'!D7="", 'Other Sheet'!D6, 'Other Sheet'!D7)
>Solution :
Almost there – missing a couple of &
ActiveCell.Formula = _
"=IF('Other Sheet'!D" & Row2 & "="""", 'Other Sheet'!D" & Row1 & ", 'Other Sheet'!D" & Row2 ")"