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

[pawcoyote]

Hi,

I am very new to VBA and was wondering if there was a way to do the following.

When entering a State Name in cell Y7 it will put the Region it belongs to in cell AD7

I have a column with States. I am trying to put a VBA formula in another cell to look at that column, if the state is Washington, California, or Oregon, I want the result to be "West". If it's another range of states, it might return "Central", etc.Basically trying to tie state names for which region they are in.

[Paul_Hossler]

Easiest way is to put

=IFERROR(VLOOKUP(Y7,States!$A:$B,2,FALSE),"Not Found")

in AD7, and use a 'data base' sheet like in the attachment (named 'States')

Or were you looking for something more complicated?

[pawcoyote]

Easiest way is to put

=IFERROR(VLOOKUP(Y7,States!$A:$B,2,FALSE),"Not Found")

in AD7, and use a 'data base' sheet like in the attachment (named 'States')

Or were you looking for something more complicated?

Thank you I will try this...

[pawcoyote]

Easiest way is to put

=IFERROR(VLOOKUP(Y7,States!$A:$B,2,FALSE),"Not Found")

in AD7, and use a 'data base' sheet like in the attachment (named 'States')

Or were you looking for something more complicated?

Hi Paul, that is what I want to do thanks. IT works great, now how would I put that into a VBA script to run automatically when I enter a State into A3 and so on?

[mike7952]

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

[Paul_Hossler]

It can be done using Worksheet Events, but you'll need to be a little more specific as to cells

E.g. in #1 you said state in Y7 and region in AD7

In #4 you said state in A3

I'd need to know the cells where you might put a state and the cells where the region goes

[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...

[mike7952]

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

Yes that would be great

[pawcoyote]

Here is the sample file with the two worksheets on it and the data...

[mike7952]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
 Const Column_Y As Long = 25
 If Target.Column = Column_Y Then
    Target.Offset(, 5) = Evaluate("=IFNA(INDEX(Names!K:K,MATCH(""" & Target.Value & """,Names!J:J,0)),""Not Found"")")
 End If
End Sub

[pawcoyote]

I will test it out thank you!

[mike7952]

I will test it out thank you!

Add the code to the MDS Equipment Detail worksheet module

[pawcoyote]

I have added the code to the worksheet view code worksheet area. But it isn't working. Nothing is populating Field AD on the MDS Equipment Detail sheet.

states_worksheet.jpg

[Paul_Hossler]

I'd add a little more to it

1. You need Application.EnableEvents = False to prevent the event handler for triggering itself

2. It is possible that multiple col Y cells are changed at once (Paste, Control-Enter, etc.) so you would need to handle each one

3. If it's not found, then I just made the Region Red, although you could make the bad state red also

4. You could use Data Validation to make a drop down list since it's easy to misspell/mistype something

5. You could allow a state abbreviation in col Y, do the region lookup and also replace the NY with 'New York'

Option Explicit

Const cStateCol As Long = 25
Const cRegionCol As Long = 30

Dim rNames As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rStates As Range, rState As Range
    Dim v As Variant

    If Intersect(Target, Me.Columns(cStateCol)) Is Nothing Then Exit Sub

    Set rStates = Intersect(Target, Me.Columns(cStateCol))
    Set rNames = Worksheets("Names").Range("J:K")

    Application.EnableEvents = False

    For Each rState In rStates.Cells
        With rState
            If Len(Trim(rState.Value)) = 0 Then
                .EntireRow.Cells(cRegionCol).ClearContents
                .EntireRow.Cells(cRegionCol).Interior.Color = xlNone
            
            Else
                On Error Resume Next
                v = Application.WorksheetFunction.VLookup(.Value, rNames, 2, False)
                On Error GoTo 0
                
                If IsEmpty(v) Then
                    .EntireRow.Cells(cRegionCol).ClearContents
                    .EntireRow.Cells(cRegionCol).Interior.Color = vbRed
                Else
                    .EntireRow.Cells(cRegionCol).Value = v
                    .EntireRow.Cells(cRegionCol).Interior.Color = xlNone
                End If
            End If
        End With
    Next

    Application.EnableEvents = True
End Sub

[pawcoyote]

I'd add a little more to it

1. You need Application.EnableEvents = False to prevent the event handler for triggering itself

2. It is possible that multiple col Y cells are changed at once (Paste, Control-Enter, etc.) so you would need to handle each one

3. If it's not found, then I just made the Region Red, although you could make the bad state red also

4. You could use Data Validation to make a drop down list since it's easy to misspell/mistype something

5. You could allow a state abbreviation in col Y, do the region lookup and also replace the NY with 'New York'

Option Explicit

Const cStateCol As Long = 25
Const cRegionCol As Long = 30

Dim rNames As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rStates As Range, rState As Range
    Dim v As Variant

    If Intersect(Target, Me.Columns(cStateCol)) Is Nothing Then Exit Sub

    Set rStates = Intersect(Target, Me.Columns(cStateCol))
    Set rNames = Worksheets("Names").Range("J:K")

    Application.EnableEvents = False

    For Each rState In rStates.Cells
        With rState
            If Len(Trim(rState.Value)) = 0 Then
                .EntireRow.Cells(cRegionCol).ClearContents
                .EntireRow.Cells(cRegionCol).Interior.Color = xlNone
            
            Else
                On Error Resume Next
                v = Application.WorksheetFunction.VLookup(.Value, rNames, 2, False)
                On Error GoTo 0
                
                If IsEmpty(v) Then
                    .EntireRow.Cells(cRegionCol).ClearContents
                    .EntireRow.Cells(cRegionCol).Interior.Color = vbRed
                Else
                    .EntireRow.Cells(cRegionCol).Value = v
                    .EntireRow.Cells(cRegionCol).Interior.Color = xlNone
                End If
            End If
        End With
    Next

    Application.EnableEvents = True
End Sub

Hi, I do have a Data Validation being used on the State Field. All State Names are on the Names Sheet Column K and have a =OFFSET COUNTA enabled on them as well.

[Paul_Hossler]

Not the Data Validation I meant

Capture.JPG


Capture2.JPG

[pawcoyote]

Not the Data Validation I meant

Capture.JPG


Capture2.JPG

Correct... It all works on the Sample sheet you sent back. Looks great and does what I want. I added in the Data Validation like I did on my other sheet and it works... but when I copy the code to my other workbook and sheet "View Code" "Worksheet "Change" like on the Sample Doc, nothing happens when I change the State field.

mds_state_worksheet.jpg

[Paul_Hossler]

Hard to tell from your picture


1. Double check your addresses

State in in Col Y, and the lookup table in in Names J:K

2. Make sure that you're not disabling Events someplace else

Put a break point on the first line in the event handler, enter a state, and make sure the event handler is called

Single step through the handler and see what's happening

Capture.JPG

[pawcoyote]

Thank you, I am redoing the Workbook. I have had a lot of trial by error's on that one. I am going to create a master and only put in the working VBA, Formulas and Macro's. Everyone has been a huge help to me and I am very appreciative of it. I am a very new to VBA and trying to under stand it all as I go.

Again thank you and I will post back once I get it cleaned up and working.

[pawcoyote]

I updated a new sheet and the code works thank you so much for all your help.