How to return multiple match values from Selected Criteria

Mnet22

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

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
891
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.
 

Mnet22

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

Well-known Member
Joined
Jan 19, 2006
Messages
891
The code doesn't populate the JobBox, have you filled it with unique items from column A?
 

Mnet22

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

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
891
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
 

Mnet22

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

Mnet22

New Member
Joined
Sep 17, 2017
Messages
30
Hi Robert, I have a problem running this script in Excel 2016 - it works perfectly in 2007? Any Suggestions? Thanks in Advanced!
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

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