VBA: Find 2 cell values in the same row and display contents in listbox

[ob1kin0bi]

I have a userform that has a txtZip, Listbox1, & ComboBox1, cmdGetCity

Short Story: I want to search for the Zipcode (txtZip) and State (ComboBox1) columns - and if they match the row of data, display the name(s) of the Cit(ies) of that row (Column D) in Listbox1.

Long Story:
I want a function that whenever cmdGetCity is clicked, it will search the B2:B81832 range for a zipcode that matches txtZip (entered by user) and, also, search E2:E81832 for data that the users selects in the Combobox 1 (State - selected by user). All entries that are found, I would like all items that match those two criteria ti display column D's (name of Cities) contents in Listbox1.


This is what I have, but I keep getting an error 'Type Mismatch':

Private Sub cmdGetCity_Click()

If (txtZip = Worksheets("Zipcodes").Range("B2:B81832")) & (ComboBox1 = Worksheets("Zipcodes").Range("E2:E81832")) Then
ListBox1.List = Worksheets("Zipcodes").Text("D")

End If

End Sub

 

[lrobbo314]

Does it really need to search for the state? Won't matching up the zip code do it all? Isn't searching for the state matches redundant at that point?

 

[ob1kin0bi]

well, I will get less results if I use both zip and state.

 

[ob1kin0bi]

Here's all my code here:

Option Explicit

Private Sub cmdGetCity_Click()
Dim rowhit As Integer

If IsError(Application.Match(txtZip, Worksheets("Zipcodes").Range("B2:B81832"), 0)) = False And IsError(Application.Match(ComboBox1.Text, Worksheets("Zipcodes").Range("E2:E81832"), 0)) = False Then
If Application.Match(txtZip, Worksheets("Zipcodes").Range("B2:B81832"), 0) = Application.Match(ComboBox1.Text, Worksheets("Zipcodes").Range("E2:E81832"), 0) Then
MsgBox "both txtZip & Combobox1.text are on the same row in their respective ranges"
rowhit = Application.Match(txtZip, Worksheets("Zipcodes").Range("B2:B81832"), 0)
ListBox1.Text = Worksheets("Zipcodes").Range("D" & rowhit)
End If
End If
End Sub

Private Sub UserForm_Initialize()
    Dim e As Variant
    For Each e In SortArray(UniqueValues(Sheet1.Range("E2:E81832")))
        ComboBox1.AddItem e
    Next e
End Sub
 
Function SortArray(ByRef MyArray As Variant, Optional Order As Long = xlAscending) As Variant
    Dim w As Worksheet
    Dim r As Range
     
    Set w = ThisWorkbook.Worksheets.Add()
     
    On Error Resume Next
    Range("A1").Resize(UBound(MyArray, 1), 1) = WorksheetFunction.Transpose(MyArray)
    Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = WorksheetFunction.Transpose(MyArray)
    Set r = w.UsedRange
    If Order = xlAscending Then
        r.Sort Key1:=r.Cells(1, 1), Order1:=xlAscending
    Else
        r.Sort Key1:=r.Cells(1, 1), Order1:=xlDescending
    End If
     
    SortArray = r
     
    Set r = Nothing
    Application.DisplayAlerts = False
    w.Delete
    Application.DisplayAlerts = True
    Set w = Nothing
End Function
 
Public Function UniqueValues(theRange As Range) As Variant
    Dim colUniques As New VBA.Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Excel.Range
    Dim i As Long
    Dim vUnique As Variant
    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
        If vCell <> vLcell Then
            If Len(CStr(vCell)) > 0 Then
                colUniques.Add vCell, CStr(vCell)
            End If
        End If
        vLcell = vCell
    Next vCell
    On Error GoTo 0
     
    ReDim vUnique(1 To colUniques.Count)
    For i = LBound(vUnique) To UBound(vUnique)
        vUnique(i) = colUniques(i)
    Next i
     
    UniqueValues = vUnique
End Function

 

[lrobbo314]

Sorry but that doesn't make sense to me. A zip code defines an area within a state. Zip codes starting with 112 are in Brooklyn, NY. How would giving it an extra search parameter for the state, New York, give you fewer results?

 

[ob1kin0bi]

Some Zipcodes are the same for different cities in different states. Also, there may be many cities in the same state that share the same zip.