Embedding an IF/AND statements within VBA Code

[louisepr]

Hi All

I am trying to replicate this simple formula =IF(AND(L15="Yes", M15="Yes", N15="Yes"),"OK"," ") that works fine in Excel, but cannot get it to work in VBA

ActiveCell.FormulaR1C1 = "=IF(AND(RC[-6]=""Yes"", RC[-5]=""Yes"", RC[-4]=""Yes""),""OK""", """")"
Range("R3").Select
Selection.AutoFill Destination:=Range(Cells(3, 18), Cells(row1 - 1, 18)), Type:=xlFillDefault

Does anyone have a suggestion?

 

[Peter_SSs]

You have an extra quote mark as shown.

 

[Peter_SSs]

BTW, you don't need to 'Select' anything to do most things in vba and Selecting slows your code.
Try this instead

Range("R3:R" & row1 - 1).FormulaR1C1 = "=IF(AND(RC[-6]=""Yes"", RC[-5]=""Yes"", RC[-4]=""Yes""),""OK"", """")"

 

[louisepr]

You have an extra quote mark as shown.

View attachment 39242

Well, I feel like a right doofus! Thank you

 

[Peter_SSs]

Well, I feel like a right doofus! Thank you

No problem - very easy to miss when there are so many in there.