Hi,

If data is filtered, for example in a table, VLOOKUP still returns the same data.  Filtering has no effect.

Maybe if you explain what you are trying to do we can find a solution.  Please supply a workbook with data and clear explanations of what you want and the expected result.

Thanks

Phil

Philip Treacy said

Please supply a workbook with data and clear explanations of what you want and the expected result.

Thanks

Phil  

I can't help without your workbook.

How exactly are you stuck?

Hi,

When the Validation sub runs you have these 3 lines of code at the top

   LR = Range("U" & Rows.Count).End(xlUp).Row
   LR1 = Range("B" & Rows.Count).End(xlUp).Row
   LR2 = Range("B" & Rows.Count).End(xlUp).Row

But these are acting on the active sheet which is FORM therefore they are all set to 1.

LR1 and LR2 are set to correct values later in your code, after you switch to other sheets.  But LR is not set to any other value so the code that relies on it is not working properly.

At the top of the validation sub, activate the sheet you want before setting values for LR, LR1 and LR2.

Regards

Phil

Hi,

I'm a bit confused.  I don't know what sheet you want the code to act upon.

All you need is a line like this

  Sheets("PIR Template").Activate

before the 3 lines that set values for LR, LR1 an LR2. 

I'm guessing that's the sheet you want but as I said, I don't know for sure.  How can I know? You need to tell me what sheet you want the code to work with.

Regards

Phil

Seems to be, but again, you haven't clearly explained the problem or desired result to I'm guessing.  Have you tried it?

The code does enter a VLOOKUP only for visible cells in AA.

If you are getting some other result please attach the workbook again so I can see what's happening.

You're welcome