VBA Insert Formula and copy to last row in column/Table

[Kpersen]

I have a table where in column CM I want to insert a formula in cell# CM3 and then copy that formula to all cells below until (and including) the last cell with data as per column A.

Here is my code:

Range("CM3").FormulaR1C1 = "=IF(AND(AY3=""yes"",BB3=""successful"",DZ3=""yes""),1,0)"
With Sheets("VoC Data Extract (2)")
.Range("CM3").AutoFill .Range("CM3:CM" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

When I run the macro all the cells return as #NAME ? because the formula the macro inserted look like this:
=IF(AND('AY3'="yes",'BB3'="successful",'DZ3'="yes"),1,0)

Apostrophes were added around the cells in the formula. How do I avoid this?

Thanks in advance

 

[Joe4]

Quotes and double-quotes are always problematic because text qualifiers need to be differentiated from literal quotes/double-quotes. It can make your head spin!

Here is an easy way to get the exact code that you need. Make Excel do the work for you!
Turn on your Macro Recorder, then go into cell CM3 on your sheet and manually enter the formula the way you need it to look. Then stop the Macro Recorder.
Then view your code and you will see VBA's interpretation of that formula! Then you can copy/paste that part into your VBA code.

 

[Kpersen]

Thank you Joe.

That works.

 

[Joe4]

You are welcome.