Hi Rick,

I'm trying this and it seems like what i need, but I'm getting a type
mismatch error:

Sub GetStateAbbreviation()
Dim X As Long, LastRow As Long, StatePosition As Long
Const States = "NC SC NJ FL RI TX NH ME GA CT VA CA AZ NV OR DC MD TN MI
NY MA PA MO IN KS NM IA OK AR IL"
Const StartRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For X = StartRow To LastRow
Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1), 2)
Next
End Sub

I'm a rookie, so the only row I don't really understand the logic is
"Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1), 2)". I
get lost there sorry! :$

"Rick Rothstein" wrote:

Here is a simple macro you can use...

Sub GetStateAbbreviation()
Dim X As Long, LastRow As Long, StatePosition As Long
Const States = "AB CD EF GH IJ KL MN OP QR ST"
Const StartRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For X = StartRow To LastRow
Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1), 2)
Next
End Sub

You need to replace the text I assigned to the States constant (the Const
statement) with a space delimited list of your 2-letter state abbreviations
(I would have done this, but I don't know the order you have set for the
states). I also limited the States text string to 10 states just for testing
purposes... you would, of course, put all 50 state abbreviation (space
delimited) between the quote marks... just make sure you put them in the
same order as you want the number in Column B to reflect.

--
Rick (MVP - Excel)



"McRibIsBack" wrote in message
...
Hi, I haven't used arrays in a while so forgive me. I'm converting
numeric
values that represent the U.S. states into the text values. If B1 = 1,
then
A1.value = NC, If B1 = 5, then A1.value = SC. I was making a case
structure
for this, but this will take me forever because I need to check every
state
AND I need to check the entire column "B"! I realized an array would be
better, but I can't remember how to build them correctly. Here is the
case
structure I was making:
Sub EntityCheck()

Select Case Range("B2").Value

Case 1
Range("A2").Value = "NC"

Case 5
Range("A2").Value = "SC"

Case 35
Range("A2").Value = "NJ"

Case 75
Range("A2").Value = "FL"

Case 99
Range("A2").Value = "TX"

Case 172
Range("A2").Value = "GA"

End Select


I appreciate any help you can offer!

.