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

CustomerName	Address	City	State	Zip Code	SalesRegion
Getaway Inn	234 E Cannon Ave.	Saginaw	MI	48605	East
Home Furnishings Limited	234 Heritage Ave.	Midland		48640	48640
Johnson, Kimberly	5678 S. 42nd Ave.	Rockford	IL	61125	East
Kensington Gardens Resort	12345 Redmond Rd	Milwaukee	WI	53204	Central
Hampton Village Eatery	234 Hampton Village	Springfield		62703	62703
Healthy Concepts	1234 Westown Road	West Des Moines	IA	50625	Central
International Mailing Corp.	8765 58th Street West	St. Louis	MO	63156	Central

<tbody>
</tbody>

 

[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?