Embedded AND in IF statement not working

[kc_native]

Hi folks, the following is a formula I am entering into a cell using VBA, and is the resulting formula that is in the target cell after the code runs, not the code itself. I am getting a #NAME? error, and it apparently doesn't like the Cells portion of the formula. Can someone tell me why I can't use Cells(x,y) in this case? I need to keep this formula variable, in case the number of data columns changes in the worksheet.

=IF(AND(Cells(3,14)>0,Cells(3,15)<>""),"X","")

 

[Jon Peltier]

Cells() is a VBA command, so Excel doesn't recognize it as a worksheet function.

Try

SomeCell.Formula = "=IF(AND(" & ActiveSheet.Cells(3,14). Address & ">0," & ActiveSheet.Cells(3,15).Address & "<>""""),""X"","""")"

 

[kc_native]

Cells() is a VBA command, so Excel doesn't recognize it as a worksheet function.

Try

SomeCell.Formula = "=IF(AND(" & ActiveSheet.Cells(3,14). Address & ">0," & ActiveSheet.Cells(3,15).Address & "<>""""),""X"","""")"

Thanks Jon! The only problem I see is that it creates a formula with the loacation locked ($Column$Row), but I want to take this formula and copy it down the column a variable number of rows. Is there a way to write the code to not make the cell position in the formula, absolute?

 

[Jon Peltier]

.Address is the default, and provides absolute references.

.Address(RowAbsolute:=False, ColumnAbsolute:=False) overrides the default, providing relative references.