Need Help with a VBA issue in Excel

[dohara]

To start I am a noob. I literally just looked a VBA for the first time yesterday. So I appreciate if you dumb up your replies. lol.

A couple times a week I am given a spread sheet at work. I have to split the zip codes and move them to there perspective Store. There is about 20 zip codes and although I use the sort option, It still takes me awhile. I want to use the marco to pretty much give each zipcode a store.

Here is my issue. I am trying to look at "J1" if the zip code matches one of many I want "Bullhead" to be written in "M1"

I was able to do this, It took me several hours of trial and error to come up with the best result. I tried a lot of different things. (At the very bottom is what I came up with)

Here is the problem. I need to do this all the way down the spread Sheet. ie. if J3 = 86409 M3 = Kingman. If J4 = 86409 M4 = Kingman. So on and so on all the way to J5000,M5000.

Any help would be greatly appreciated. What I want to do is simple but I could not find the answer on my own, or I could not understand it. I assume I will have to start over. And take a different approach. Not sure what though.

Sub MoversBirthdays()
Dim zipcode As Long, Store As String
zipcode = Range("J2").Value
If zipcode = "86426" Or "86427" Or "86429" Or "86430" Or "86435" Or "86436" Or "86437" Or "86438" Or "86439" Or "86440" Or "86442" Or "86446" Or "89028" Or "89029" Or "89046" Or "92304" Or "92332" Or "92363" Then Store = "Bullhead" Else: Store = "Kingman"
If zipcode = "" Then Store = ""
Range("M2").Value = Store
End Sub

 

[KB]

If you wish to take the VBA macro approach I would make it a function and put the function in the spreadsheet cell M4 like =MOversBirthdays(J4)

When you drag down the column M4 it will auto increment the value passed in from cell J5 J6 J7.. to the function

Function MoversBirthdays(zipcode As String)
Dim Store As String

If zipcode = "86426" Or zipcode = "86427" Or zipcode = "86429" Or zipcode = "86430" Or zipcode = "86435" Or zipcode = "86436" Or zipcode = "86437" Or zipcode = "86438" Or zipcode = "86439" Or zipcode = "86440" Or zipcode = "86442" Or zipcode = "86446" Or zipcode = "89028" Or zipcode = "89029" Or zipcode = "89046" Or zipcode = "92304" Or zipcode = "92332" Or zipcode = "92363" Then Store = "Bullhead" Else: Store = "Kingman"
If zipcode = "" Then Store = ""
MoversBirthdays = Store

End Function

A better way to do this is with the VLOOKUP function. Maintain a sheet of zipcodes and their corresponding stores. Then use VLOOKUP to bring the name over:
http://www.techonthenet.com/excel/formulas/vlookup.php

 

[dohara]

Ty for the response. I want the one click approach.

 

[dohara]

Problem Solved

Sub MoversBirthdays()
Dim varZip As Variant
Dim arrStore() As String
Dim StoreIndex As Long
With Range("G2", Cells(Rows.Count, "G").End(xlUp))
If .Row < 2 Then Exit Sub 'No data
ReDim arrStore(1 To .Rows.Count)
For Each varZip In .Value
StoreIndex = StoreIndex + 1
Select Case varZip
Case 86426 To 86427, 86429 To 86430, 86435 To 86440, 86442, 86446, 89028 To 89029, 89046, 92304, 92332, 92363
arrStore(StoreIndex) = "Bullhead"
Case ""
arrStore(StoreIndex) = ""
Case Else
arrStore(StoreIndex) = "Kingman"
End Select
Next varZip
End With
If StoreIndex > 0 Then Range("I2").Resize(StoreIndex).Value = Application.Transpose(arrStore)
End Sub