VBA Help

[Tennisguuy]

I don't know much about how to write VBA code but I had a situation I wanted a statement to appear if certain counties were in AR. I had the states in column W17:W130 and the counties X17:X130 on the spreadsheet.

Function ArkansasWind(myRange As Range) As String

    Dim i As Long
    Dim counties As Variant
    Dim myCount As Long
    Dim countyCount As Long
    Dim myString As String

    Application.Volatile

    ' Enter array of counties
    counties = Array("SALINE", "PULASKI", "LONOKE", "DESHA", "WHITE", "CLEBURNE", "WASHINGTON", "INDEPENDENCE")

    ' Loop through counties
    For i = LBound(counties) To UBound(counties)
        ' Count the number of times each entry appears in the range
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "AR")
        ' Add to string if count is greater than 0 and increase county count
        If myCount > 0 Then
            myString = myString & counties(i) & ", "
            countyCount = countyCount + 1
        End If
    Next i

    ' See if any records returned
    Select Case countyCount
    Case 0
        ArkansasWind = ""
    Case 1
        ArkansasWind = Left(myString, Len(myString) - 2) & " ARKANSAS COUNTY HAS HIGH WINDS."
    Case Else
        ArkansasWind = Left(myString, Len(myString) - 2) & " ARKANSAS COUNTIES HAVE HIGH WINDS."
    End Select

End Function

On the sheet in cell A17 I entered this formula Arkansaswind(W17:W130)


Now I have a situation where I have multiple states and multiple counties and I tried to modify the above code but its not working. I think because I am not sure how to match the individual state with the exact counties. I entered the counties in the same position as the states.

For example if the state is AZ and the county is Gila I wanted the statement to list the state or states and counties with this text include HAS HIGH FLOOD FREQUENCIES. For example if AZ is listed with Gila I wanted it to say Gila, AR county has high flood frequencies. I modified the above VBA code with the below. Some states can have over 60 counties I just listed a few in example below but in the VBA code I show them all

Function Flood(myRange As Range) As String

    Dim i As Long
    Dim counties As Variant
    Dim myCount As Long
    Dim countyCount As Long
    Dim myString As String

    Application.Volatile

    ' Enter array of counties
    counties = Array("APACHE", "COCONINO", "COCHISE", "GILA")
    counties = Array("DEL NORTE", "SISKIYOU", "SONOMA", "MARIN")
    counties = Array("WALTON", "HOLMES", "WASHINGTON", "JACKSON", "BAY", "CALHOUN")
    counties = Array("PICKENS", "CHEROKEE", "ROCKDALE", "GILA")
    counties = Array("LAKE", "DU PAGE", "COOK", "GILA", "WHTESIDE")
    counties = Array("WARREN", "FOUNTAIN", "VERMILLION", "PARKE", "MONTGOMERY", "VIGO")
    counties = Array("EMMET", "WINNEBAGO", "WORTH", "PALO ALTO")
    counties = Array("CADDO", "BOSSIER", "WEBSTER", "IBERIA", "TERREBONNE")
    counties = Array("ANDROSCOGGIN", "AROSTOOK", "CUMBERLAND", "FRANKLIN")
    counties = Array("HINDS", "RANKIN", "COPIAH", "SIMPSON")
    counties = Array("DAVIESS", "BUCHANNON", "CLINTON", "CALDWELL", "LIVINGSTON")
    counties = Array("ALBANY", "SUFFOLK", "SULLIVAN")
    counties = Array("ALFALFA", "GRANT", "CHEROKEE", "ADAIR")
    counties = Array("STEWARD")
    counties = Array("BRISCO", "WILLIAMSON")
    counties = Array("CACHE", "RICH", "WEBER", "TOOELE", "UTAH", "JUAB", "SANPETE", "KANE")

    ' Loop through counties
    For i = LBound(counties) To UBound(counties)
        ' Count the number of times each entry appears in the range
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "AZ")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "CA")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "FL")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "GA")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "IL")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "IN")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "IO")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "LA")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "ME")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "MS")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "MO")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "NY")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "OK")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "TN")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "TX")
        myCount = Application.CountIfs(myRange, counties(i), myRange.Offset(0, -1), "UT")
        ' Add to string if count is greater than 0 and increase county count
        If myCount > 0 Then
            myString = myString & counties(i) & ", "
            countyCount = countyCount + 1
        End If
    Next i

    ' See if any records returned
    Select Case countyCount
    Case 0
        Flood = ""
    Case 1
        Flood = Left(myString, Len(myString) - 2) & " HAS HIGH FLOOD FREQUENCIES."
    Case Else
        Flood = Left(myString, Len(myString) - 2) & " HAS HIGH FLOOD FREQUENCIES."
    End Select

End Function

 

[JoeMo]

There are a few things about your code I don't understand.
If the states are in W17:W130 and the counties in X17:X130, why wouldn't the entry in A17 be: Arkansaswind(X17:X130) rather than: Arkansaswind(W17:W130)?

In the modified code, what is the reason for the long series of reassignments of the variable "counties"? Only the final assignment will be used in the code that follows.

If you have a list of states and counties already present in a worksheet, why not build 2-D arrays directly from there like:

Dim StatesAndCounties as Variant
StatesAndCounties = Range("W17:X130").Value

Then you can work with this array directly.

 

[Tennisguuy]

Sorry, you're correct it should have been X17:X130 for Arkansaswind. That was a typo. As for the modified code. I don't really know how to write VBA code so I was just using that code as an example. In the Arkansaswind code there was only one state. In the new one I need I have multiple states. So I just added the states where the state was in the Arkansaswind example and the counties in the counties area. I knew it probably wouldn't work since I have multiple states and that need to be matched to the exact counties. The reason for the long series of counties in the modified version each separate one represents the counties that will need to be match to an individual state. I entered the counties in the order as I entered the state. The first row of counties need to be match to the first row of the state. For example, all of the counties in the first row would apply to the of AZ, all of the counties in the second row would apply to the state CA and so forth. I didn't know how to modified the code so I tried that.

I can kind of understand what you're saying but I don't know how to revise the code.

 

[Tennisguuy]

If I remove all the states and counties except for one the modified code works just as in the first code I posted. It's when I have multiple states to match with multiple counties as in the modified code is where things stop working.

 

[Tennisguuy]

When you say the states and counties are already in the workbook do you mean on the worksheet? If so then no they are not always listed. The worksheet is a template which is used on various accounts so the states and counties will be different in each case. Sometimes the state listed in the modified example might not even be listed. I understand in the Arkansaswind and the modified version that it would only applies to one state and county.

I understood the change you made to the variant but not sure about the second change or what you man by 2-D arrays. Assuming you mean two dimensional arrays. I knew I needed to change the array to match them to the states but just didn't know you. I tried putting the state in the array like this

counties = Array = "AZ" =("APACHE", "COCONINO", "COCHISE", "GILA") but this didn't work.

 

[Tennisguuy]

Is this where I change the variant

Function Flood(myRange As Range) As String

Dim i As Long
Dim StatesandCounties As Variant
Dim myCount As Long
Dim countyCount As Long
Dim myString As String

Application.Volatile

 

[Tennisguuy]

Do I replace the followong

counties = Array("APACHE", "COCONINO", "COCHISE", "GILA")

With

statesandcounties = Array("APACHE", "COCONINO", "COCHISE", "GILA")