VBA code to paste to last row

ragnar12

Board Regular
Joined
May 1, 2013
Messages
119
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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,""""))))))"
 
Upvote 0
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.
 
Upvote 0
It works for me. Try commenting out .Value=.Value and check if the formula is entered correctly.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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