How to return multiple match values from Selected Criteria

[Mnet22]

I have two columns called "Job" and "Activity" - There can be multiple activities for one job (But they differ for each job) e.g.

For Job X the Activity can be Certification, Assembly, Painting, etc. For EACH of the Numbered Items it can be Assembly, Inspection, Painting, etc.

Is there a script to loop through and select the correct Activity? I have a Userform if the Job Combo-box is selected it must only show the selected activities in the Activity Combo-box?? Any suggestion on how to do it will be greatly appreciated.

<tbody>
[TD="class: xl67"]Job[/TD]
[TD="class: xl67, width: 226"]Activity ID[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl69"]Certification[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl69"]Assembly[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl69"]Painting[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl69"]Testing[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl70"]Certification[/TD]

[TD="class: xl68"]X[/TD]
[TD="class: xl70"]Inspection[/TD]

[TD="class: xl68"]Tooling[/TD]
[TD="class: xl71"]Fabrication[/TD]

[TD="class: xl68"]Tooling[/TD]
[TD="class: xl71"]Inspection[/TD]

[TD="class: xl68"]Tooling[/TD]
[TD="class: xl71"]Maintenance[/TD]

[TD="class: xl68"]Tooling[/TD]
[TD="class: xl71"]Testing[/TD]

[TD="class: xl68"]Tooling[/TD]
[TD="class: xl71"]Design[/TD]

[TD="class: xl72, width: 174"] 1581120-30/01[/TD]
[TD="class: xl69"]Assembly[/TD]

[TD="class: xl72, width: 174"] 1581130-30/01[/TD]
[TD="class: xl69"]Inspection[/TD]

[TD="class: xl72, width: 174"] 1582525-01/01[/TD]
[TD="class: xl69"]Painting[/TD]

[TD="class: xl72, width: 174"] 1582550-01/01[/TD]
[TD="class: xl69"]Testing[/TD]

[TD="class: xl72, width: 174"] 1582700-0712/01[/TD]
[TD="class: xl70"]Certification[/TD]

[TD="class: xl72, width: 174"] 1583100-06/02[/TD]
[TD="class: xl70"]Fabrication[/TD]

[TD="class: xl72, width: 174"] 1583110-01/01[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl72, width: 174"] 1583110-2001/01[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl72, width: 174"] 1583120-20/02[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl72, width: 174"] 1583120-22/02[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl72, width: 174"] 1583120-23/02[/TD]
[TD="class: xl68"][/TD]

</tbody>

 

[sericom]

You could add the following to your JobBox change event

ActivityBox.Clear
With Worksheets("Sheet1").Range("A2:A1000")
    Set c = .Find(JobBox.Text, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            ActivityBox.AddItem (c.Offset(, 1).Text)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

You'll need to adjust the two combobox names, sheet name and job list range to suit.

 

[Mnet22]

Dear Sericom thanks a million for helping me. I have tried it but it is not working - so I quickly created a userform with just the two combo boxes and named it JobBox and ActivityBox respectively and the data in sheet 1 range A2:A1000 and it is still not working - nothing is displayed?

 

[sericom]

The code doesn't populate the JobBox, have you filled it with unique items from column A?

 

[Mnet22]

Dear Sericom the JobBox have a Row Source that points to the correct NameRange created for it so it is populated it is just not showing the activities - must the activityBox also have a row source?

 

[sericom]

No, you wouldn't set the row source for activity box, the code should populate it. Are you sure you placed it in the JobBox change event?

Private Sub JobBox_Change()
    ActivityBox.Clear
    With Worksheets("Sheet1").Range("A2:A1000")
        Set c = .Find(JobBox.Text, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Me.ActivityBox.AddItem (c.Offset(, 1).Text)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub

 

[Mnet22]

It is working!! Thank you so much for all the time, effort and fantastic work it is greatly appreciated!!! The only problem is with the job numbers it must only show Assembly, Inspection, Painting, Testing, Certification and Fabrication to choose from now if you select a job it will only show what was written next to it in the corresponding column e.g. fabrication. I will send you the dataset - Thanks in Advacnce you are a superstar

 

[Mnet22]

Hi Robert, I have a problem running this script in Excel 2016 - it works perfectly in 2007? Any Suggestions? Thanks in Advanced!

 

[Mnet22]

Solved - was a reference setting in VBA!

 

[Mnet22]

Hi Robert, Is there a way I can Autofilter or search the Jobbox?