How to do Vlookup using VBA to sheet which is filtered?

[dmadhup]

Hi,

I am doing vlookup to filtered sheet using this code:

Sub test()
Sheets("Sheet1").Select
Range("B2:B6").SpecialCells(xlVisible).FormulaR1C1 = "=VLOOKUP(rc1,Sheet2!c1:c3,2,FALSE)"
End Sub

This code works well when sheet1 is filtered. But In my case Sheet2 is filtered not sheet1.

I want to do vlookup from sheet1 (Not filtered) to the sheet2 (filtered). Is there any way I can modify my code to achieve this?

Any help is appreciated.
Thank you!

 

[yky]

You right. That error is gone. But I am getting blank values which I need to check formula.
It means exceeds the Array size.

By the way in this line ROW($B$2:$B$10000)-ROW($B$14). From which sheet it gets the data?

For example:
OFFSET('[Master_Quote.xlsx]Quote_History1'!$B$2:$B$10000,ROW($B$2:$B$10000)-ROW($B$14),0,1)

Does line ROW($B$2:$B$10000)-ROW($B$14) get data from Master_Quote.xlsx?

You need to change $B$14 to $B$2 in "ROW($B$2:$B$10000)-ROW($B$14)".

It doesn't matter which sheet it refers to. This part generates an array of {0,1,2,3.....9999}.

 

[dmadhup]

You need to change $B$14 to $B$2 in "ROW($B$2:$B$10000)-ROW($B$14)".

It doesn't matter which sheet it refers to. This part generates an array of {0,1,2,3.....9999}.

I see.
Thank you so much for your help and solving my problem.
I know its been long discussion....Appreciated!

 

[yky]

You're welcome.