Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
Nevermind, I'll use VLOOKUP instead.
"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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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 :( "Don Guillett" wrote: Why not a simple VLOOKUP function using a table? 1 NC -- Don Guillett Microsoft MVP Excel SalesAid Software "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! . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
=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 On Mon, 1 Feb 2010 12:47:03 -0800, McRibIsBack wrote: 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 :( "Don Guillett" wrote: Why not a simple VLOOKUP function using a table? 1 NC -- Don Guillett Microsoft MVP Excel SalesAid Software "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! . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
Why don't you give the macro I posted a try...
-- Rick (MVP - Excel) "McRibIsBack" wrote in message ... 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 :( "Don Guillett" wrote: Why not a simple VLOOKUP function using a table? 1 NC -- Don Guillett Microsoft MVP Excel SalesAid Software "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! . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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! . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
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? -- Rick (MVP - Excel) "McRibIsBack" wrote in message ... 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! . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Array
Awesome thank you it works great!!!!!!!!! My delicate typing ligaments thank
you! "Gord Dibben" wrote: =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 On Mon, 1 Feb 2010 12:47:03 -0800, McRibIsBack wrote: 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 :( "Don Guillett" wrote: Why not a simple VLOOKUP function using a table? 1 NC -- Don Guillett Microsoft MVP Excel SalesAid Software "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! . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Array | Excel Discussion (Misc queries) | |||
[HELP WITH ARRAY] Simple for you, very hard for me....please | Excel Programming | |||
Simple Array Looping | Excel Programming | |||
Simple Array question | Excel Programming | |||
Simple Array question | Excel Programming |