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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,114,659
Messages
5,549,268
Members
410,905
Latest member
Extjel
Top