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

I have some data in col Z. If i found "Error" in col Z then i want to do vlookup in AA. If there is no "Error" in col Z then i dont want to Vlookup it in column AA.

Now i'm stuck in the below code

If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
With Sheets("PIR Template")
With .Range("A1:AH1")
.AutoFilter
.AutoFilter Field:=26, Criteria1:="Error"
End With

Range("AA" & cell.Row).Select.Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"

End With

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?

i will provide you the workbook with macro file

Simply saying i want to Vlookup a filtered data only for visible cell

I provided the information in Macro file and i also facing my macro button "Validation" not working properly
Could you please help on that also ? 

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

Thanks for the solution
But i don't know how to do that
Can you make changes and send the revised file for me if possible ?

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

did you tried to run macro like that ?
Is it working fine ?

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

Yes i tried it
Now it is working fine for me
Thanks for the support
I have one more question - in last i put VLookup
But i need vlookup for visible cells only. Can you please tell me to put Vlookup for visible cells only in col AA

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.

Okay will finish all the code today or tomorrow 
If any issues will contact you here again
Thank you so much again for your support Philip

You're welcome