VBA code to paste to last row

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
I need this formula:

=IF(J2="ACQ_PCT",P2,(IF(J2="AGP_PCT",P2,IF(J2="AWP_PCT",P2,IF(J2="MAC_PCT",P2,IF(J2="SUB_PCT",P2,""))))))

to paste from F2 to the the last row in F and the cells are blank until the formula is pasted (and possibly as seen by the formula blank still after). Columns A thru E are filled completely until the end of the data if that helps for an auto fill code. I actually have 3 formulas that this will be used for in columns F, G and H; all the formulas are different. This is for a macro that will be executed with Ctrl + W, and format a large set of data. (I only include all of this extra information because I am not sure if it helps)

I tried:

Sub test()
Dim lastRow As Long

With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
With .Range("F2:F" & lastRow)
.Formula = "=IF(J3="ACQ_PCT",P3,(IF(J3="AGP_PCT",P3,IF(J3="AWP_PCT",P3,IF(J3="MAC_PCT",P3,IF(J3="SUB_PCT",P3,""))))))"
.Value = .Value
End With
End With
End Sub

But it see's the " within the IF function and thinks that its the end of the statement...not sure how else to write the IF statement to make it not think this.

Thank you guys. I am still very new to VBA, I have always just written formulas, but I am trying to teach myself the ins and outs of VBA now so any explainations of my errors will be appreciated.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You need to d9ouble up the quotes

Code:
.Formula = "=IF(J3=""ACQ_PCT"",P3,(IF(J3=""AGP_PCT"",P3,IF(J3=""AWP_PCT"",P3,IF(J3=""MAC_PCT"",P3,IF(J3=""SUB_PCT"",P3,""""))))))"
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
Thank you VoG,

This worked great for letting the formula be accepted, but now it only runs on F1 and F2. There is something else wrong with my formula that it isn't letting it go to the last row.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It works for me. Try commenting out .Value=.Value and check if the formula is entered correctly.
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
Thanks, I got it working. I don't know what happened the first couple of times I ran it. I did have to tweak the formula to be 2 instead of 3 though, that was my bad. I appreciate the help and I am glad I learned the double " rule.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

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
Top