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]

SO I have merged the cells

which is fine

No it is not, never use merged cells, they are an abomination & should be avoided like the plague.
They cause no end of problems, with formulae, sorting filtering & VBA hates them.

 

[hmltnangel]

Edited the post I do know what you mean - I hate using them. Is there anyway to get it to skip a row, if I unmerge them?

 

[Fluff]

Do you mean you only want the code to look at every row in the filtered table?

 

[hmltnangel]

I would like it to take the filtered data, then input it to the new location as it does., but skip a row (ie - leave a blank row) after each value it inputs

I was toying with just recording a macro to copy paste the values and skip a row each time it pastes

 

[Fluff]

In that case try

      For Each Cl In .ListColumns("EmplId").DataBodyRange.SpecialCells(xlVisible)
         i = i + 1
         Ary(i, 1) = Cl.Value
        i = i + 1
      Next Cl

 

[hmltnangel]

Fluff, you are a genius. Thank you

I was so close as well, I was trying i=i+2, and also adding an extra i=i+1 - but before the Ary line of code.

 

[Fluff]

You're welcome & thanks for the feedback.