VBA: Need to find 2 cell values in the same row and display in listbox

[ob1kin0bi]

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

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 i would like to be displayed in Listbox1.

This is what I have, but I keep getting an error 'Type Mismatch' - I'm rusty on vb, but this is my first time doing vba for excel:

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

 

[Roderick_E]

Hi, you can just match range to range like this. More like:

[SIZE=3]if iserror(application.match(txtZip,[COLOR=#333333]Worksheets("Zipcodes").Range("B2:B81832"),0))=false and [/COLOR]iserror(application.match(Combobox1.text,[COLOR=#333333]Worksheets("Zipcodes").Range("E2:E81832"),0))=false then
if [/COLOR] application.match(txtZip,[COLOR=#333333]Worksheets("Zipcodes").Range("B2:B81832"),0)=[/COLOR]application.match(Combobox1.text,[COLOR=#333333]Worksheets("Zipcodes").Range("E2:E81832"),0) then
msgbox "both txtZip & Combobox1.text are on the same row in their respective ranges"
rowhit = [/COLOR]application.match(txtZip,[COLOR=#333333]Worksheets("Zipcodes").Range("B2:B81832"),0)
[/COLOR][/SIZE]ListBox1.text = Worksheets("Zipcodes").range("D" & rowhit)
endif
endif

You can't update a listbox's list directly. Need to additem

 

[ob1kin0bi]

Thanks Roderick! As far at the rowhit variable, do I need to dim that as integer since we're dealing with zip codes? I tried it, but nothing is populating in the listbox.

Here's all my code:

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

 

[Roderick_E]

rowhit is just the row number where the data match. So yes, Dim is as Long will work. Try temporarily replacing ListBox1.Text = Worksheets("Zipcodes").Range("D" & rowhit) with msgbox Worksheets("Zipcodes").Range("D" & rowhit) to test what is being returned. If still nothing, try: msgbox rowhit to see if there is a match

 

[ob1kin0bi]

This is my code for the command button, nothing ever happened when clicked.

Private Sub cmdGetCity_Click()
Dim rowhit As Long

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)
MsgBox rowhit
End If
End If
End Sub

 

[Roderick_E]

Nothing happened??? Sounds strange, right after the Dim statement, for testing put:

msgbox "test"
exit sub

Then try clicking the command button. If the test doesn't return, something is wrong with how you have the command button set up or whether macros are enabled.

 

[ob1kin0bi]

That displays a msgbox with the word "test".

 

[Roderick_E]

That displays a msgbox with the word "test".

Ok we established the code is firing. Somewhere the IF conditions aren't being met