Using IF statement with variable row numbers in VBA

Question

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)

Tim Williams · Accepted Answer · 2022-12-14 05:13:01Z

1

Almost there - missing a couple of &

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

answered Dec 14, 2022 at 5:13

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

TinMan · Accepted Answer · 2022-12-14 06:10:31Z

0

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

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

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

answered Dec 14, 2022 at 6:10

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Collectives™ on Stack Overflow

Using IF statement with variable row numbers in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related