IF , OR formula not working in VBA

Question

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?

Answer 1

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

Answer 2

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