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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,714
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
50,714
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
50,714
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,470
Messages
5,572,307
Members
412,453
Latest member
Parbiana
Top