VBA Error For Formula AutoFill To Last Row

[Worker8ee]

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

 

[footoo]

IF" & _ should be IF(RC[-2]=" & _

 

[footoo]

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 :

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

 

[Worker8ee]

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!

 

[Fluff]

If your interested you can also slim down your formula like

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)

 

[Worker8ee]

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.

 

[Fluff]

You're welcome