Simple Array

[McRibIsBack]

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!

 

[Don Guillett]

Why not a simple VLOOKUP function using a table?
1 NC

 

[McRibIsBack]

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]

Nevermind, I'll use VLOOKUP instead.

 

[Ryan H]

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

 

[Rick Rothstein]

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.

 

[McRibIsBack]

I'm trying that, but when I copy the forumula it tries to updates the cells,
but I need the formula in a few hundred rows so i just need it to update the
lookup value and NOT the lookup vector. Is that possible?

what's happening now:

This is copied from A2: "=LOOKUP(B2, P1:Q30, Q1:Q30)"
To A3 as: "=LOOKUP(B3, P2:Q31, Q2:Q31)"

What I need to happen:

A3 = "=LOOKUP(B3, P1:Q30, Q1:Q30)"
A4 = "=LOOKUP(B4, P1:Q30, Q1:Q30)" etc....

How do I fill the cells and only have the lookup value updated? It will
take me forever to manually fix each cell one by one

 

[Gord Dibben]

=LOOKUP(B2,$P$1:$Q$30,$Q$1:$Q$30)

To see the reason for this go to help on absolute and relative referencing


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

Why don't you give the macro I posted a try...

 

[McRibIsBack]

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]

I'm guessing you understand the Mid function, but to review... the first
argument is the text you want to parse and the third argument is how many
characters to pull out; now, for the second argument... this is just the
position in the text that you want to start pulling characters from. I may
have made a bad assumption about this part... I assumed your numbers in
Column B would be 1, 2, 3 (sequentially) up to the number of last state (my
code, as currently written, is completely dependent on this); but now, in
looking back at your original post, I see your numbering may not be
sequential (since, for example, 172 is the value for GA). Can you clarify
how your number codes in Column B relate to the state abbreviations?

 

[McRibIsBack]

Awesome thank you it works great!!!!!!!!! My delicate typing ligaments thank
you!