VBA Nested IF and VLOOKUP

[Machl22]

Good Afternoon, All. I am attempting to use VBA to update "Area" values in Column A of my Weekly FFA tab based on a VLOOKUP of "District" values in Column B from the table in the "Districts" tab. The script must also fill down Column A based on the length of Column B. There are a few naming convention issues with some of the "District" names, and thus I am using a series of Nested IFs to align these Districts to their appropriate Districts. Unfortunately, I am running into Runtime Error 1004 Application -defined or object-defined error. Any assistance is greatly appreciated.

With ThisWorkbook.Sheets("Weekly FFA")
    .Range("A6:A" & .Range("B" & .Rows.Count).End(xlUp).Row).Formula = "=IF(B6='COLO./WYOMING','WESTPAC',IF(B6='CONNECTICUT VALLEY PFC','ATLANTIC',IF(B6='GREATER BOSTON PFC','ATLANTIC',IF(B6='NORTHERN NEW ENGLAND PFC','ATLANTIC',IF(B6='PHILADELPHIA METRO','ATLANTIC',IF(B6='GREATER SC','SOUTHERN',(UPPER(VLOOKUP(B6,Districts!B:C,2,FALSE)))))))))"
End With

 

[Fluff]

Your values ned to be wrapped in double quotes like

"=IF(B6=""COLO./WYOMING"",""WESTPAC"",IF(B6=""CONNECTICUT VALLEY PFC"",""ATLANTIC"","

 

[Machl22]

Your values ned to be wrapped in double quotes like

"=IF(B6=""COLO./WYOMING"",""WESTPAC"",IF(B6=""CONNECTICUT VALLEY PFC"",""ATLANTIC"","

Thank you very much, Fluff. I'll give it a shot.

 

[Fluff]

You can also write that formula like

    .Range("A6:A" & .Range("B" & .Rows.Count).End(xlUp).Row).Formula = "=IF(B6=""COLO./WYOMING"",""WESTPAC"",IF(OR(B6={""CONNECTICUT VALLEY PFC"",""GREATER BOSTON PFC"",""NORTHERN NEW ENGLAND PFC"",""PHILADELPHIA METRO""}),""ATLANTIC"",IF(B6=""GREATER SC"",""SOUTHERN"",(UPPER(VLOOKUP(B6,Districts!B:C,2,FALSE))))))"

 

[Machl22]

Thank you very much, Fluff. I'll give it a shot.

Of course it was a syntax issue! Thanks, Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.