Log In
Register
Home
August 3, 2020
Offline
Hello everyone
I need help for how to perform Vlookup for filtered data in VBA
October 5, 2010
Offline
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
August 3, 2020
Offline
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
October 5, 2010
Offline
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?
August 3, 2020
Offline
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 ?
October 5, 2010
Offline
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
August 3, 2020
Offline
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 ?
October 5, 2010
Offline
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
August 3, 2020
Offline
did you tried to run macro like that ?
Is it working fine ?
October 5, 2010
Offline
Seems to be, but again, you haven't clearly explained the problem or desired result to I'm guessing. Have you tried it?
August 3, 2020
Offline
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
October 5, 2010
Offline
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.
1 Guest(s)
