VBA Code to Match US Region to the State that is entered

[pawcoyote]

Using Pauls uploaded workbook add the states to column A on the Example sheet and run macro

Sub abc()
 Dim i As Long
 Dim rng As Range, cell As Range
 With Worksheets("states")
    arr = .Range("a1").CurrentRegion.Value
 End With
 With Worksheets("Example")
    Set rng = .Range("a1", Cells(Rows.Count, "a").End(xlUp))
 End With
 With CreateObject("scripting.dictionary")
    For i = 1 To UBound(arr)
        .Item(arr(i, 1)) = arr(i, 2)
    Next
    For Each cell In rng
        If .exists(cell.Value) Then
            cell.Offset(, 1) = .Item(cell.Value)
        Else
            cell.Offset(, 1) = "Not Found"
        End If
    Next
 End With
End Sub

See attached workbook: StatesRegions (1).xlsm

Hi, I have been modifying my Worksheets with the info provided. Here is what I have and doing...

Worksheet MDS Equipment Detail Column Y7 is the first cell to key in a State. Column AD7 is where I want the matching Region to go.

Worksheet Names: I have all my look up values there, I have created Named Ranges for each State and Region as listed below.

names_state {...} =OFFSET(Names!$J$2,0,0,COUNTA(Names!$J$2:$J$200),1)
names_region {...} =OFFSET(Names!$L$2,0,0,COUNTA(Names!$K$2:$K$100),1)

If you want to look at the spreadsheet let me know...