VBA - to Lookup a value in table - Then return all matches

[hmltnangel]

Afternoon all, `

I have had a search but couldnt see anything quite like what I need. I have a nice big table called "ID". In this table there are a list of staff ID numbers in column L. And in Column AS we have the Staff members Line Mangers ID number.

What I would like to do is a bit of VBA, to lookup a value in cell C4 (Free text box where you input a Manager ID number), within the "ID" table. then return the ID number all the staff who have this individual as the Manager. Each Staff member should have their own row. So even just list the Staff IDs in column B from Row 11 downwards

Does that make sense? And is it possible?

Normally I can find a bit of VBA to tailor to my needs, but this one I am stumped.

 

[Fluff]

Where is the table?
Also if it is a structured table what are the two column names?

 

[hmltnangel]

Table is in another tab called "roster" in the same workbook

It is a structured table. Column IDs are "EmplId" and "Manager Emplid"

 

[Fluff]

Ok, how about

Sub hmltnangel()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
  
   With Sheets("Roster").ListObjects("ID")
      ReDim Ary(1 To .ListRows.count, 1 To 1)
      .Range.AutoFilter .ListColumns("Manager Emplid").Index, Sheets("Master").Range("C4")
      For Each Cl In .ListColumns("EmplId").DataBodyRange.SpecialCells(xlVisible)
         i = i + 1
         Ary(i, 1) = Cl.Value
      Next Cl
   End With
   Sheets("Master").Range("B11").Resize(i).Value = Ary
End Sub

 

[hmltnangel]

Hmmm, nearly. It autofilters the table, but no results are found.

Error 1004
No Results found

Then Debug highlights
For Each Cl In .ListColumns("Emplid").DataBodyRange.SpecialCells(xlVisible)

 

[hmltnangel]

Oooh, It seems to be a Text/Number formatting error.

I done text to columns in the Manager Emplid Column and it seems to be better now.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[hmltnangel]

No problem. Thanks for the help twice in the day :D

 

[Fluff]

My pleasure

 

[hmltnangel]

Not one for dragging my old threads back up - but this is directly related. The code above has worked great, but an update means each staff member needs two rows on the new table for them. So I have merged the cells, which is *edit - not fine and is an abomination, but when the code picks out the data and pastes it in, it doesnt follow the merged cells.

For example if it finds four staff ids - it inputs two of the IDs, but misses the other two. Can I make it input a value, then skip a row before continuing?