VBA Nested IF and VLOOKUP

Machl22

New Member
Joined
Jun 16, 2018
Messages
11
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,044
Office Version
  1. 365
Platform
  1. Windows
Your values ned to be wrapped in double quotes like
VBA Code:
"=IF(B6=""COLO./WYOMING"",""WESTPAC"",IF(B6=""CONNECTICUT VALLEY PFC"",""ATLANTIC"","
 

Machl22

New Member
Joined
Jun 16, 2018
Messages
11
Your values ned to be wrapped in double quotes like
VBA Code:
"=IF(B6=""COLO./WYOMING"",""WESTPAC"",IF(B6=""CONNECTICUT VALLEY PFC"",""ATLANTIC"","
Thank you very much, Fluff. I'll give it a shot.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,044
Office Version
  1. 365
Platform
  1. Windows
You can also write that formula like
VBA Code:
    .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))))))"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,044
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,872
Messages
5,544,796
Members
410,635
Latest member
phoenix7771
Top