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:

dsaffo

Board Regular
Joined
Mar 16, 2006
Messages
111
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
 

nosslived

New Member
Joined
May 11, 2010
Messages
9
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:

Forum statistics

Threads
1,081,695
Messages
5,360,679
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top