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!

 

[dmadhup]

I see that you have $B:$B in other places of the formula. Change them all to $B14:$B500 and see what happens.

Where are you pointing to?
I used B:B whenever there is
[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B

Because In file [Master_Quote_NewClosedQuote_History.xlsx]Quote_History1 I want to look whole column where data get added every day.

Or you pointing somewhere else?

 

[yky]

I'm talking about the very first $B:$B (This only applies to the post you quoted above. I have pointed out in another post that the $B:$B also need to be changed). Remember before I forgot to replace 6 with 7 in some places of the formula while having $A$2:$A$7, generating a mix of $B$2:$B$6 with $A$2:$A$7, and you got incorrect results? In your case, I believe all arrays need to have the same size. You can't mix $B:$B with $B14:$B500. I'd suggest pick a range that you think will contain all future data, something like $B14:$B50000 if your entries will not exceed 50000.

 

[dmadhup]

I'm talking about the very first $B:$B (This only applies to the post you quoted above. I have pointed out in another post that the $B:$B also need to be changed). Remember before I forgot to replace 6 with 7 in some places of the formula while having $A$2:$A$7, generating a mix of $B$2:$B$6 with $A$2:$A$7, and you got incorrect results? In your case, I believe all arrays need to have the same size. You can't mix $B:$B with $B14:$B500. I'd suggest pick a range that you think will contain all future data, something like $B14:$B50000 if your entries will not exceed 50000.

I see where you pointing to. I will give a test.

Thank you!

 

[dmadhup]

I see where you pointing to. I will give a test.

Thank you!

I tested it with same array size. But its surprising me when I read from external workbook.

Range("BB14").FormulaArray = _
"=IFERROR(INDEX(Sheet1!$B$1:$B$10000,MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!$B$1:$B$10000,ROW($B$1:$B$10000)-ROW($B$14),0,1))>0,IF(Sheet1!$B$1:$B$10000=$B14,1)),0),0),"""")"

In above code Sheet1 is in same workbook. But when I replace Sheet1 with other workbook name like below then there is error named "Unable to set FormulaArrayProperty..."

''Range("BB14").FormulaArray = _
"=IFERROR(INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000,MATCH(1,IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000,ROW($B$2:$B$10000)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000=$B14,1)),0),0),"""")"

Any clue on this?

 

[yky]

Does you data start from $B$1 or $B$14?

The following gives an array starting from {1-14, 2-14, 3-14, etc...}. You can only have 0 or positive numbers in the array. No negative numbers.

ROW($B$1:$B$10000)-ROW($B$14)

If your data starts from $B$1, then use "ROW($B$1:$B$10000)-ROW($B$1)". If it starts from $B$14, use "ROW($B$14:$B$10000)-ROW($B$14)". In the latter case, you need to change all $B$1:$B$10000 to $B$14:$B$10000 so to keep the size of arrays identical.

 

[dmadhup]

Does you data start from $B$1 or $B$14?

The following gives an array starting from {1-14, 2-14, 3-14, etc...}. You can only have 0 or positive numbers in the array. No negative numbers.

ROW($B$1:$B$10000)-ROW($B$14)

If your data starts from $B$1, then use "ROW($B$1:$B$10000)-ROW($B$1)". If it starts from $B$14, use "ROW($B$14:$B$10000)-ROW($B$14)". In the latter case, you need to change all $B$1:$B$10000 to $B$14:$B$10000 so to keep the size of arrays identical.

If I used data in same workbook it worked as shown above as Sheet1. But when I read from external workbook then there is error.

INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1 data starts from B2 shown below

And the resulted data will go to BB14 AS SHOWN BELOW

In this case I used below formula:

''Range("BB14").FormulaArray = _
"=IFERROR(INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000,MATCH(1,IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000,ROW($B$2:$B$10000)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B$2:$B$10000=$B14,1)),0),0),"""")"

 

[yky]

ROW($B$2:$B$10000)-ROW($B$14)

Change $B$14 to $B$2 and see what happens.

 

[dmadhup]

ROW($B$2:$B$10000)-ROW($B$14)

Change $B$14 to $B$2 and see what happens.

Same error "Unable to set FormulaArray Property of the range class"

But when I just use .Formula here :- Range("BB14").Formula

It doesn't give me above error. But the result is not correct. So, something with FormulaArray.....

 

[yky]

I just checked the length of that line and it's 324. I believe there is a limit of 255 characters per line for FormulaArray. Maybe you can shorten the file names.

 

[dmadhup]

I just checked the length of that line and it's 324. I believe there is a limit of 255 characters per line for FormulaArray. Maybe you can shorten the file names.

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?