How to return multiple match values from Selected Criteria

Mnet22

New Member
Joined
Sep 17, 2017
Messages
37
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.

JobActivity ID
XCertification
XAssembly
XPainting
XTesting
XCertification
XInspection
ToolingFabrication
ToolingInspection
ToolingMaintenance
ToolingTesting
ToolingDesign
1581120-30/01Assembly
1581130-30/01Inspection
1582525-01/01Painting
1582550-01/01Testing
1582700-0712/01Certification
1583100-06/02Fabrication
1583110-01/01
1583110-2001/01
1583120-20/02
1583120-22/02
1583120-23/02

<tbody>
</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You could add the following to your JobBox change event

Code:
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.
 
Upvote 0
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?
 
Upvote 0
The code doesn't populate the JobBox, have you filled it with unique items from column A?
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?

Code:
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
 
Upvote 0
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 ;)
 
Last edited:
Upvote 0
Hi Robert, I have a problem running this script in Excel 2016 - it works perfectly in 2007? Any Suggestions? Thanks in Advanced!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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