Macro using Find/Replace to enter a function

[goobee]

I have a small macro that uses the State entries to add Sales Regions to a spreadsheet. The macro adds a new column right of the Zip Code column, copies over the States and then uses the find/replace function to enter the Sales Regions. It's most likely not the most efficient method but it more or less works for us. The issue we are having is when the State is blank, there is nothing to find and replace. Ideally, when State fields are blank, we would like to pull in the Zip Codes. I'm thinking this may be accomplished with a function to point the Sales Region cell to the corresponding Zip Code cell location. Is this possible? I've included snippets of the macro and data/desired output. (Fake data used) If not, alternate suggestions will be appreciated. Thanks for any assistance.

Sub Add_SalesRegions()

Application.ScreenUpdating = False

Rows(1).Find("State").EntireColumn.Select
Selection.Copy
Rows(1).Find("Zip Code").Offset(, 1).Select
ActiveSheet.Paste
Rows(1).Find("Zip Code").Offset(, 1).Select
ActiveCell = "SalesRegion"
Cells.EntireColumn.AutoFit
Rows(1).Find("SalesRegion").EntireColumn.Select

Selection.Replace what:="IA", Replacement:="Central", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace what:="IL", Replacement:="East", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace what:="MI", Replacement:="East", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace what:="MO", Replacement:="Central", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace what:="WI", Replacement:="Central", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Cells.EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

[TABLE="class: grid, width: 832, align: center"]
<tbody>[TR]
[TD]CustomerName[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip Code[/TD]
[TD]SalesRegion[/TD]
[/TR]
[TR]
[TD]Getaway Inn[/TD]
[TD]234 E Cannon Ave.[/TD]
[TD]Saginaw[/TD]
[TD]MI[/TD]
[TD="align: right"]48605[/TD]
[TD]East[/TD]
[/TR]
[TR]
[TD]Home Furnishings Limited[/TD]
[TD]234 Heritage Ave.[/TD]
[TD]Midland[/TD]
[TD][/TD]
[TD="align: right"]48640[/TD]
[TD="align: right"]48640[/TD]
[/TR]
[TR]
[TD]Johnson, Kimberly[/TD]
[TD]5678 S. 42nd Ave.[/TD]
[TD]Rockford[/TD]
[TD]IL[/TD]
[TD="align: right"]61125[/TD]
[TD]East[/TD]
[/TR]
[TR]
[TD]Kensington Gardens Resort[/TD]
[TD]12345 Redmond Rd[/TD]
[TD]Milwaukee[/TD]
[TD]WI[/TD]
[TD="align: right"]53204[/TD]
[TD]Central[/TD]
[/TR]
[TR]
[TD]Hampton Village Eatery[/TD]
[TD]234 Hampton Village[/TD]
[TD]Springfield[/TD]
[TD][/TD]
[TD="align: right"]62703[/TD]
[TD="align: right"]62703[/TD]
[/TR]
[TR]
[TD]Healthy Concepts[/TD]
[TD]1234 Westown Road[/TD]
[TD]West Des Moines[/TD]
[TD]IA[/TD]
[TD="align: right"]50625[/TD]
[TD]Central[/TD]
[/TR]
[TR]
[TD]International Mailing Corp.[/TD]
[TD]8765 58th Street West[/TD]
[TD]St. Louis[/TD]
[TD]MO[/TD]
[TD="align: right"]63156[/TD]
[TD]Central[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub goobee()
   Dim State As Range
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("IA", "Central", "IL", "East", "MI", "East", "MO", "Central", "WI", "Central")
   Set State = Range("1:1").Find("State", , , xlWhole, , , False, , False)
   With Cells(1, Columns.Count).End(xlToLeft)
      State.EntireColumn.Copy .Offset(, 1)
      .Offset(, 1).Value = "SalesRegion"
      For i = 0 To UBound(Ary) Step 2
         .Offset(, 1).Replace Ary(i), Ary(i + 1), xlWhole, , False, , False, False
      Next i
      With Range(.Offset(, 1), Cells(Rows.Count, .Column).End(xlUp).Offset(, 1))
         .SpecialCells(xlBlanks).FormulaR1C1 = "=rc[-1]"
         .Value = .EntireColumn.Value
      End With
   End With
End Sub

 

[goobee]

Awesome, thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

However I've just noticed that there's a slight problem, it should be

Sub goobee()
   Dim State As Range
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("IA", "Central", "IL", "East", "MI", "East", "MO", "Central", "WI", "Central")
   Set State = Range("1:1").Find("State", , , xlWhole, , , False, , False)
   With Cells(1, Columns.Count).End(xlToLeft)
      State.EntireColumn.Copy .Offset(, 1)
      .Offset(, 1).Value = "SalesRegion"
      For i = 0 To UBound(Ary) Step 2
         .Offset(, 1).Replace Ary(i), Ary(i + 1), xlWhole, , False, , False, False
      Next i
      With Range(.Offset(, 1), Cells(Rows.Count, .Column).End(xlUp).Offset(, 1))
         .SpecialCells(xlBlanks).FormulaR1C1 = "=rc[-1]"
         [COLOR=#ff0000].Value = .Value[/COLOR]
      End With
   End With
End Sub

 

[goobee]

I think I broke it. For whatever reason, it is only pulling the States in now and not the Zones. That's my motto, "If it ain't broke, I'll keep working on it until it is."

 

[Fluff]

What code are you currently using?