Searching through data in multiple sheets VBA

nosslived

New Member
Joined
May 11, 2010
Messages
9
Hello,

I have been trying to figure this out for sometime now and just can't get it.

I have a userform with two comboboxes that are used for the search criteria (ComboBox1 and ComboBox2). The comboboxes are populated based on unique values. ComboBox1 list training cities and combobox 2 lists training dates. No problems there.

I have a listbox (ListBox1) that I want to populate with all the people who took a training class in the selected city and date.

Here is the problem:

The trainees’ demographic information (including their name) is located on one Sheet ("PN") while the specific training record is located on a sheet called ("MAT"). Each person has a unique record number in column A of both sheets that is generated when the a new person is added. Their name is in column B of the PN sheet. The problem is that some people may not take the MAT training so the row numbers will not correspond between sheets and I'm also sorting names alphabetically by last name, so that will throw the row numbers off, too.

What I want it to do is search the record numbers in the MAT sheet then find those record numbers in the PN sheet and return the name of the trainee(s) with the associated record number in the listbox.

The code below will return the name of the person with the corresponding row number which is not what I want it to do.

Please help!
_________

Code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim y As Single
Dim City As String
Dim myDate As String
Dim ws As Worksheet
Dim ws2 As Worksheet
 
Set ws = Worksheets("PN")
Set ws2 = Worksheets("MAT")
On Error Resume Next
City = ComboBox1.Value
myDate = ComboBox2.Value
 
'Clear ListBox
ListBox1.Clear
'Find matching dates and add companies if the dates match
For i = 3 To 1000    
    If ws2.Range("J" & i).Value = City Then
    If ws2.Range("B" & i).Value = myDate Then ListBox1.AddItem ws.Range("B" & i).Value
    End If
    If ws2.Range("R" & i).Value = City Then
    If ws2.Range("K" & i).Value = myDate Then ListBox1.AddItem ws.Range("B" & i).Value
    End If
    If ws2.Range("Z" & i).Value = City Then
    If ws2.Range("S" & i).Value = myDate Then ListBox1.AddItem ws.Range("B" & i).Value
    End If
    If ws2.Range("AH" & i).Value = City Then
    If ws2.Range("AA" & i).Value = myDate Then ListBox1.AddItem ws.Range("B" & i).Value
    End If
    If ws2.Range("AP" & i).Value = City Then
    If ws2.Range("AI" & i).Value = myDate Then ListBox1.AddItem ws.Range("B" & i).Value
    End If

Next i

Label4.Caption = ListBox1.ListCount
 
End Sub
_______

If you have any questions, please let me know.
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
If the cities match, you will need to create another loop to look for the right row in the PN worksheet.
Code:
Private Sub CommandButton1_Click()
   Const ID_COLUMN = 1
   
   Dim i As Long
   Dim y As Single
   Dim City As String
   Dim myDate As String
   Dim ws As Worksheet
   Dim ws2 As Worksheet
   Dim sId As String
   Dim iPNRow As Long
   
   Set ws = Worksheets("PN")
   Set ws2 = Worksheets("MAT")
   On Error Resume Next
   City = ComboBox1.Value
   myDate = ComboBox2.Value
   
   'Clear ListBox
   ListBox1.Clear
   'Find matching dates and add companies if the dates match
   For i = 3 To 1000
      sId = ws2.Cells(i, ID_COLUMN)
      
      If ws2.Range("J" & i).Value = City Then
         iPNRow = Find_PN_Row(sId)
         If ws2.Range("B" & i).Value = myDate And iPNRow <> 0 Then
            ListBox1.AddItem ws.Range("B" & Find_PN_Row(sId)).Value
            GoTo NEXT_ROW
         End If
      End If
      If ws2.Range("R" & i).Value = City Then
         If ws2.Range("K" & i).Value = myDate And iPNRow <> 0 Then
            ListBox1.AddItem ws.Range("B" & Find_PN_Row(sId)).Value
            GoTo NEXT_ROW
         End If
      End If
      If ws2.Range("Z" & i).Value = City Then
         If ws2.Range("S" & i).Value = myDate And iPNRow <> 0 Then
            ListBox1.AddItem ws.Range("B" & Find_PN_Row(sId)).Value
            GoTo NEXT_ROW
      End If
      If ws2.Range("AH" & i).Value = City Then
         If ws2.Range("AA" & i).Value = myDate And iPNRow <> 0 Then
            ListBox1.AddItem ws.Range("B" & Find_PN_Row(sId)).Value
            GoTo NEXT_ROW
         End If
      End If
      If ws2.Range("AP" & i).Value = City Then
         If ws2.Range("AI" & i).Value = myDate And iPNRow <> 0 Then
            ListBox1.AddItem ws.Range("B" & Find_PN_Row(sId)).Value
            GoTo NEXT_ROW
         End If
      End If
   
NEXT_ROW:
   Next i
   

Label4.Caption = ListBox1.ListCount

End Sub

Private Function Find_PN_Row(sId As String) As Long
   Const ID_COLUMN = 1
   
   Dim ws As Worksheet
      Set ws = Worksheets("PN")
   Dim i As Long
   
   For i = 1 To ws.UsedRange.Rows.Count
      If ws.Cells(i, ID_COLUMN) = sId Then
         Find_PN_Row = i
         Exit For '***BREAK***
      End If
   Next i
End Function
 
Upvote 0
This worked!

I had to add an End If to the code as it was missing it, but thank you so much for this!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top