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)

CodePudding user response：

Almost there - missing a couple of &

ActiveCell.Formula = _
  "=IF('Other Sheet'!D" & Row2 & "="""", 'Other Sheet'!D" & Row1 & ", 'Other Sheet'!D" & Row2 ")"

CodePudding user response：

Kudos to Tim Williams. I just wanted to explain how I convert formulas into code.

1. Create a working formula in the ActiveCell
2. Print the Actual formula in the Immediate Window: ?ActiveCell.Formula
3. Print the test formula code in the Immediate Window: '?Replace(ActiveCell.Formula,Chr(34),String(2,34))'
4. Type ?" and the output of step 2.
5. Start building my formula

Shortcut keys: Delete Line: Ctrl y Insert Line: Ctrl Enter