VBA loop to lookup state name

[NerdNinja]

Hello, I'm trying to build a macro that can automatically look at a state abbreviated ID (TX, KS, NY, etc.) in a single column and return the full state name (Texas, Kansas, New York, etc.) in a blank column to the right. I need to be able to use this macro in any workbook, as I will refer to various reports containing state and county data. It's important to note that WHERE the State ID appears could be different on any given report. So in one report, the column containing the state ID might appear in column B, but in a different report it could appear in column D. Similarly, the data set and column headers might start on row 1 or row 4. So that's constantly changing. I have a Helper Table already set up with column A listing the state IDs and column B listing the state names, and in the past I would just use a Vlookup function to get the state name, then copy the formula down to the last populated row. But I would really like to be able to run a macro that will automatically search my active worksheet for a cell containing the column header "State ID" (for simplicity purposes let's say it's in cell A1), I would like it to insert a blank column to the right (so column B is empty), give it a column header name of "State Name", then go through each populated row looking at the state ID in column A and returning the state name in column B until it gets to the bottom of the report.

So the report I'm working in could look like this...

(A) (B)
(1) State ID County
(2) AL Bibb
(3) AL Calhoun
(4) AL Chilton
(5) AR Carroll
(6) AR Clay
(7) AZ Apache
(8) CA Kern

I'd like to run a macro and end with the report looking like this...

(A) (B) (C)
(1) State ID State Name County
(2) AL Alabama Bibb
(3) AL Alabama Calhoun
(4) AL Alabama Chilton
(5) AR Arkansas Carroll
(6) AR Arkansas Clay
(7) AZ Arizona Apache
(8) CA California Kern

Any help would be greatly appreciated!!! Thank you!!!

 

[ranman256]

col D = left(B2,2) 'state code
col E = =VLOOKUP(D2,States!$A$1:$B$58,2) 'return the 2nd column of the States table AL, Alabama

 

[NerdNinja]

I'm sorry, the table I inserted did not accurately represent my example. Please see below for better representation...

These are a couple examples of how the spreadsheets may appear... Remember, I want the macro to start by looking for the cell containing the column header "State ID", then run from there.

In this example, the "State ID" cell is in cell A1:

Excel 2010
	A	B
1	State ID	County
2	AL	Bibb
3	AL	Calhoun
4	AL	Chilton
5	AR	Carroll
6	AR	Clay
7	AZ	Apache
8	CA	Kern
Sheet1

But in this example, it's in cell C5:

Excel 2010
	A	B	C	D
1	blah, blah
2	header stuff
3	blah, blah
4
5	Random column	FIPS	State ID	County
6	random text	01001	AL	Bibb
7	random text	01015	AL	Calhoun
8	random text	01021	AL	Chilton
9	random text	05015	AR	Carroll
10	random text	05021	AR	Clay
11	random text	04001	AZ	Apache
12	random text	06029	CA	Kern
Sheet1

Once the cell containing the column header "State ID" is located, I want it to insert a blank column to the right, give it a column header name of "State Name"...

Excel 2010
	A	B	C
1	State ID	State Name	County
2	AL		Bibb
3	AL		Calhoun
4	AL		Chilton
5	AR		Carroll
6	AR		Clay
7	AZ		Apache
8	CA		Kern
Sheet1

OR

Excel 2010
	C	D	E
5	State ID	State Name	County
6	AL		Bibb
7	AL		Calhoun
8	AL		Chilton
9	AR		Carroll
10	AR		Clay
11	AZ		Apache
12	CA		Kern
Sheet1

...then go through each populated row looking at the state ID and return the state name until it gets to the bottom of the report. The 'Helper Table' for the Vlookup formula is titled "State ID and Name".

Excel 2010
	A	B	C
1	State ID	State Name	County
2	AL	ALABAMA	Bibb
3	AL	ALABAMA	Calhoun
4	AL	ALABAMA	Chilton
5	AR	ARKANSAS	Carroll
6	AR	ARKANSAS	Clay
7	AZ	ARIZONA	Apache
8	CA	CALIFORNIA	Kern
Sheet1

 

[NerdNinja]

Bump

 

[Fluff]

How about

Sub GetStateNames()
   
   Dim Cnt As Long
   Dim Cl As Range
   Dim Ary As Variant
   Dim fnd As Range
   Dim Dic As Object

   Set Dic = CreateObject("scripting.dictionary")

   With ActiveSheet.UsedRange
      Set fnd = .Find("State ID", , , xlWhole, , , False, , False)
      If fnd Is Nothing Then
         MsgBox "State ID not found"
         Exit Sub
      End If
         fnd.Offset(, 1).EntireColumn.Insert
   End With
   
   With Sheets("StateID and Name")
      Ary = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
   End With
   For Cnt = 1 To UBound(Ary, 1)
      If Not Dic.exists(Ary(Cnt, 1)) Then Dic.Add Ary(Cnt, 1), Ary(Cnt, 2)
   Next Cnt
   For Each Cl In Range(fnd.Offset(1), fnd.End(xlDown))
      Cl.Offset(, 1).Value = Dic(Cl.Value)
   Next Cl
End Sub

The sheet you want to add the state too need to be the active sheet.