VBA Error For Formula AutoFill To Last Row

Worker8ee

New Member
Joined
Aug 8, 2018
Messages
28
Hey all, I must be missing something really simple, I am trying to enter a formula to populate department names for their counterpart number and I keep encountering errors, the debug is highlighting the formula but I can't figure out what I am doing wrong:

Sub AutoFillFormula()
Sheets("excads").Select
With ActiveSheet
LastR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("O3").FormulaR1C1 = _
"=(IF(RC[-2]=1,""Grocery"",IF(RC[-2]=2,""Deli"",IF(RC[-2]=3,""Grocery"",IF(RC[-2]=4,""Vitamins"",IF(RC[-2]=5,""Vitamins"",IF(RC[-2]=6,""Grocery"",IF(RC[-2]=7,""Grocery"",IF(RC[-2]=8,""Vitamins"",IF(RC[-2]=9,""Grocery"",IF(RC[-2]=10,""Grocery"",IF(RC[-2]=11,""Grocery"",IF(RC[-2]=12,""Produce"",IF(RC[-2]=13,""Meat"",IF(RC[-2]=14,""Grocery"",IF(RC[-2]=15,""Vitamins"",IF" & _
"16,""Beer&Wine"",IF(RC[-2]=17,""Deli"",IF(RC[-2]=18,""Bakery"",IF(RC[-2]=19,""Sushi"",IF(RC[-2]=20,""Floral"",IF(RC[-2]=22,""Juice Bar"",IF(RC[-2]=23,""Juice Bar"",IF(RC[-2]=24,""Bakery"",IF(RC[-2]=25,""Juice Bar"",IF(RC[-2]=36,""Meat"",IF(RC[-2]=37,""Meat"",IF(RC[-2]=86,""Urban Remedy"",""""))))))))))))))))))))))))))))"
ActiveSheet.Range("O3").AutoFill Destination:=ActiveSheet.Range("O3:O" & LastR)
End With
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Here's a different way you might want to consider.

In a new sheet named "List", put in column A the number codes and the corresponding descriptions in column B.
Then :
Code:
Sub Enter_Formula()
Dim LastR&
With Sheets("excads")
    LastR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("O3:O" & LastR).Formula = "=VLOOKUP(M3,List!A:B,2)"
End With
End Sub
 
Upvote 0
Thank you footoo for your first comment and thank you a thousand times for your second comment, that is a much more simple and powerful way of doing this. These 12 hour days have been affecting my creative solution abilities. Thank you again you are great!
 
Upvote 0
If your interested you can also slim down your formula like
Code:
Sub AutoFillFormula()
Sheets("excads").Select
With ActiveSheet
   lastr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
   ActiveSheet.Range("O3:O" & lastr).FormulaR1C1 = "=(IF(OR(RC[-2]={1,3,6,7,9,10,11,14}),""Grocery"",if(rc[-2]=2,""Deli"",IF(OR(RC[-2]={4,5,8,15}),""Vitamins"",IF(RC[-2]=12,""Produce"",IF(OR(RC[-2]={13,36,37}),""Meat""))))))"
End With
End Sub
(I've only done the first few)
 
Upvote 0
Ah yes thank you Fluff, I'm always interested in learning how to maximize efficiency and write effective code in the most compact way. Thanks for taking the time to show me that.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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