Excel macros Help. Creating a new column based of

Question

I am trying to write a Macros in excel to create a new column with these states divided into these regions. I keep getting runtime error 13

Here is the code I have so far.

Sub Region ()

Dim Pacific As Variant
Pacific = Array("WA", "OR", "ID", "CA", "NV", "AZ", "NM", "HI", "AK")

Dim Continental As Variant
Continental = Array("AR", "IA", "CO", "KS", "LA", "MS", "MT", "ND", "NE", "OK", "SD", "UT", "WY")

Dim SouthEast As Variant
SouthEast = Array("GA", "AL", "FL", "SC", "KY", "TN")

Dim Midwest As Variant
Midwest = Array("MN", "WI", "IL", "IN", "MI", "OH")

Dim NorthAtlantic As Variant
NorthAtlantic = Array("ME", "NH", "MA", "RI", "CT", "VT", "NY", "PA", "NJ", "DE", "MD", "WV", "VA", "NC")

Dim Texas As Variant
Texas = Array("TX”)

Dim state As String , result As String
score = Range("F1").Value
If state = Pacific Then
    result = "PACIFIC"
ElseIf state = Continental Then
    result = "Continental"
ElseIf state = SouthEast Then
    result = "SouthEast"
ElseIf state = Midwest Then
    result = "Midwest"
ElseIf state = NorthAtlantic Then
    result = "North Atlantic"
ElseIf state = Texas Then
    result = "Texas"
Else
    result = "fail"
End If
Range("Z1").Value = result 
End Sub

Answer 1

Em why don,t you use Access create tables as you did and then link to further logical tables you are going to create (I presume there is some practical use of the code you wrote) That is why access was created in the first place...

Answer 2

AFAIK, to search for the occurrence of a string within an array isn't a simple matter within VBA. You either have to use a loop, or possibly use WorksheetFunction.Match.

A simpler way may be to avoid arrays altogether - your code could be easily refactored to use a Select Case statement:

Sub Region ()

    Dim state As String , result As String
    state = Range("F1").Value

    Select Case state
        Case "WA", "OR", "ID", "CA", "NV", "AZ", "NM", "HI", "AK"
            result = "PACIFIC"
        Case "AR", "IA", "CO", "KS", "LA", "MS", "MT", "ND", "NE", "OK", "SD", "UT", "WY"
            result = "Continental"
        Case "GA", "AL", "FL", "SC", "KY", "TN"
            result = "SouthEast"
        Case "MN", "WI", "IL", "IN", "MI", "OH"
            result = "Midwest"
        Case "ME", "NH", "MA", "RI", "CT", "VT", "NY", "PA", "NJ", "DE", "MD", "WV", "VA", "NC"
            result = "North Atlantic"
        Case "TX"
            result = "Texas"
        Case Else
            result = "fail"
    End Select

    Range("Z1").Value = result 
End Sub

---

Note: You also had two code problems.

1. You had

   score = Range("F1").Value
   

   when I think you meant

   state = Range("F1").Value
   

2. You had "TX” instead of "TX" - I'm not sure whether the ” causes a problem in your version of Excel, but it does in mine.

---

To extend this function so that it applies to all cells in column F, you will need to loop through each row:

Sub Region ()

    Dim state As String , result As String
    Dim lastRow As Long
    Dim r As Long

    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        For r = 1 to lastRow

            state = .Cells(r, "F").Value

            Select Case state
                Case "WA", "OR", "ID", "CA", "NV", "AZ", "NM", "HI", "AK"
                    result = "PACIFIC"
                Case "AR", "IA", "CO", "KS", "LA", "MS", "MT", "ND", "NE", "OK", "SD", "UT", "WY"
                    result = "Continental"
                Case "GA", "AL", "FL", "SC", "KY", "TN"
                    result = "SouthEast"
                Case "MN", "WI", "IL", "IN", "MI", "OH"
                    result = "Midwest"
                Case "ME", "NH", "MA", "RI", "CT", "VT", "NY", "PA", "NJ", "DE", "MD", "WV", "VA", "NC"
                    result = "North Atlantic"
                Case "TX"
                    result = "Texas"
                Case Else
                    result = "fail"
            End Select

            .Cells(r, "Z").Value = result
        Next
    End With
End Sub