VBA Loop Roster to Label.Caption (so very close)

[stratthaslam]

Hi all,

Thanks in advance for your help. What I have is a Userform that I want to populate team member names based on which supervisor is selected in a combobox. The code I have now can overwrite all the label captions but overwrites all of the with the same name instead of each individual name. So a team with 17 names shows with 17 Bob's instead of all 17 people's names.

Here is what I have...just a little tweaking needed but can't quite figure it out

Dim MyRng As Range
Set MyRng = Sheets("Roster").Range("A2:A250")
For Each cell In MyRng
If cell.Value = Supervisor.Value Then
For q = 1 To 25
Me.Controls("Name" & q).Caption = cell.Offset(0, 1)
Next q
End If
Next cell

 

[Norie]

You don't seem to be changing the offset for cell, it's always 1.

Perhaps if you just replace 1 with the loop variable q.

 

[stratthaslam]

Hi Norie,

The problem is I don't need the offset to change, the value I want to return is always one column to the right of the cell. I think the error is occuring somewhere in this section

For Each cell In MyRng
If cell.Value = Supervisor.Value Then

Because it almost behave like once it finds the first match of the value of "Supervisor" it overwrites all the captions with its first match...your thoughts?

 

[Norie]

Why do you have the 2nd loop?

That's looping through all 25 labels and putting the same caption into each one.

 

[stratthaslam]

The first loop is intended to loop through the roster on the defined worksheet to match with the value in supervisor and the second loop is intended to return the separate names into the captions. For example:

A B
Supervisor Name
Matthews Jones, Robert
Matthews Richards, Mark
Matthews King, George
Shepard Nolan, Richard
Shepard Smith, Franz

So I intended the first loop to look through column A for the value in Supervisor and the second loop to return each the name in column B each time the Supervisor occurs to the caption

Make sense?

 

[Norie]

It makes sense but it's not what your code is doing.

The code loops through until it finds a match, it then populates labels Name1-Name25 with the value coming from the column to the right of he match.

It does this for each match.

For example if you pick Matthews from your example then the first match is in row 2 and the name Jones, Robert will be put in each label.

But there's also a match in row 3, so the name Jones, Robert will be replaced with Richards, Mark.

Wait a minute though, there's a matc hin row 4 too - so Richards, Mark will be replaced with King, George.

If you want to populate the first 3 labels with the 3 names try this.

Option Explicit
Private Sub Supervisor_Change()
Dim MyRng As Range
Dim cell As Range
Dim q As Long
 
    Set MyRng = Sheets("Roster").Range("A2:A6")

    For Each cell In MyRng

        If cell.Value = Supervisor.Value Then
            q = q + 1

            Me.Controls("Name" & q).Caption = cell.Offset(0, 1)
 
        End If

    Next cell
    
End Sub

 

[stratthaslam]

Norie,

Thanks much for your help. Your code worked beautifully. You are an asset and a gem on MrExcel