CODEWITHSUNDEEP
excelvbaif-statementconditional-statements
Alex Gordon
Alex Gordon

Reputation: 60871

VBA: Use First Formula in IF-OR Statement

I have this formula in VBA:

ActiveCell.FormulaR1C1 = "=IF(OR(A2=""ETG_C"",A2=""ETGC""),C2,""na"")"

when I look at the activecell it actually returns this formula:

=IF(OR('A2'="ETG_C",'A2'="ETGC"),$B:$B,"na")

how do I force it to adhere to the first formula?

Upvotes: 0

Views: 2509

Answers (2)

Harry
Harry

Reputation: 256

the reason why it's showing $B:$B is because your VBA formula contains C2. Excel's VBA is interpreting this as the 2nd column in the worksheet because you are using the FormulaR1C1 method.

Put C2 in double quotes.

ActiveCell.FormulaR1C1 = "=IF(OR(A2=""ETG_C"",A2=""ETGC""),""C2"",""na"")"

Upvotes: 1

dwo
dwo

Reputation: 3636

ActiveCell.FormulaR1C1 uses the other kind of cell addresses, where you name them row 1 col 1. Use ActiveCell.Formula!

Upvotes: 3

Related Questions