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

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
180
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,942
Office Version
  1. 365
Platform
  1. Windows
Where is the table?
Also if it is a structured table what are the two column names?
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
180
Table is in another tab called "roster" in the same workbook

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,942
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

Board Regular
Joined
Aug 25, 2010
Messages
180

ADVERTISEMENT

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

Board Regular
Joined
Aug 25, 2010
Messages
180
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,942
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,942
Office Version
  1. 365
Platform
  1. Windows
My pleasure
 

Watch MrExcel Video

Forum statistics

Threads
1,112,860
Messages
5,542,929
Members
410,577
Latest member
ZvK
Top