WorksheetFunction.VLookup in Large Range

[Vanech]

Hi!

New in the forum, hope you can help me. After several hours of research in internet, I give up to ask you . Also, sorry for my awful english 

So, in resume, I have 2 Tables stored in listobjects variables: tbVenta and tbSKU.

• tbVenta has the data of daily sales for 200,000 + SKU. Every row is the sale of a specific day for a specific SKU.
• tbSKU has information regarding those SKU. Every row has the characteristics of a specific SKU.

I basically want to do a VLookUP to add in several rows in tbVenta the information in tbSKU for the SKU in said row,
I could use:

Please Login or Register  to view this content.

But I want to use my listobjects variables as reference, so im using:

Please Login or Register  to view this content.

The problem is that Im using a For, Next loop to apply the VLookup to every cell, and there’s 200,000+ cells in tbVenta. So it takes ages to complete the process. But, if I simple apply manually “.FormulaR1C1 = "=VLOOKUP()"” to the whole range, it only takes a cumple seconds to evaluate it.

Is there a way to apply “Application.WorksheetFunction.VLookup()” to the whole range of tbVenta.ListColumn(“Canal”)

As you would see in the code, Im also interested in deleting the row if the VLookup returns empty. But I know I can do that later in code, im more concerned with the VLookup not taking ages.

Thanks.

Please Login or Register  to view this content.

[Norie]

There's no way to use Application.WorksheetFunction.VLookup like that on entire range.

The nearest you might get would be to use Evaluate along with the original VLOOKUP formula and INDEX.

You could try using arrays I suppose.

[Vanech]

I can’t use Evaluate + Application.WorksheetFunction.VLookup. Right?
And I can’t use listobjects variables in a .FormulaR1C1 = "=VLOOKUP()", not even with Evaluate. Right?

Somebody has an Idea how can I manage to do the VLookup (or similar results) using as reference the listobject variables and the header + index method?

[Norie]

Not sure what you mean about not using the listobject variables in .FormulaR1C1.

You could use them but it would be basically the same as reference the tables in the formula.

By the way, what are you looking up?

If it's the same thing for each of the VLOOKUPs it might be an idea to switch to using MATCH.

That could be used, once, to get the row number - something like this.

Please Login or Register  to view this content.

[Vanech]

But, with the Match alternative, I would still have the problem of a 200,000 repetitions For Next loop. Don’t I?

Actually, I already solved it. You were right, I could use my listobject variables in the FormulaR1C1 using tbSKU.Name. Finally this is my code:

Thanks

Please Login or Register  to view this content.

[rorya]

FYI, you can use Application.Vlookup or WorksheetFunction.Vlookup with an array of lookup values.

[Norie]

Rory

Never knew that, always resorted to using Evaluate for something like that.

[Vanech]

Rorya,

Can you post a example code of it? Would highly appreciate it.

Thanks you.

[rorya]

It would be something like:

Please Login or Register  to view this content.

[shg]

I'd speculate using Find would be faster on an unsorted list. Or sort on the search column and use a range lookup.