I made a user defined function. Put this code in a standard module. Then
put this formula in B2, "=StateNumber(A2)". Since I don't know your method
of numbering states you will have to edit the numbers. I just used 1-50 for
example. Hope this helps! If so, let me know, click "YES" below.


Function StateNumber(Number As String) As String

Dim Numbers As Variant
Dim States As Variant
Dim i As Long

Numbers = Split("1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20" & _
"21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37" & _
"38 39 40 41 42 43 44 45 46 47 48 49 50")

States = Split("AL AK AZ AR CA CO CT DE FL GA HI ID IL IN IA " & _
"KS KY LA ME MD MA MI MN MS MO MT NE NV NH NJ " & _
"NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT " & _
"VA WA WV WI WY")

For i = LBound(Numbers) To UBound(Numbers)
If Numbers(i) = Number Then
StateNumber = States(i)
Exit Function
End If
Next i

MsgBox Number & " is not related to a state.", vbExclamation

End Function
--
Cheers,
Ryan


"McRibIsBack" wrote:

I don't have to use an Array do I? I can just make a function to check the
value and loop through all of the cells right?

"McRibIsBack" wrote:

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!