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!
_________
_______
If you have any questions, please let me know.
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: