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

Kpersen

New Member
Joined
Jan 29, 2018
Messages
25
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top