Speed up a VLookUp loop

[wizuriel]

Hello.

I've just finished writing a formula that looks up some information for me across multiple worksheets instead of me having to manually do it. The code works perfectly, but runs super slow . It actually seems a lot faster for me just to manually write the vlookup formula and just paste it down the worksheet . I'm wondering if anyone has any tips for speeding it up? The worksheet can have something like upwards of 30k entries I need to look up.

The relevant code

Please Login or Register  to view this content.

[GeneralDisarray]

are you simply trying to replace the contents of a cell with a space wherever they don't equal some value you are looking for? something along those lines?

[wizuriel]

If the VLookUp fails it puts a space in the Cell (just cause some people complained that the #N/A looked threatening, but if it speeds up the program using those instead of my if statement I don't mind my output looking threatening)

[wizuriel]

Anyone have any tips?

[GeneralDisarray]

can you post an example of what you need that actually shows the sheet and goal you are after?

---

By the way, if you are using a formula like Vlookup and you want to eliminate #N/A from showing up with the formula errors out, just use IfError

example, instead of Vlookup(value,table,column,false) you would use, Iferror(Vlookup(value,table,column,false)," ")

The part after the comma in the IfError function can be whatever you like. Space " " or blank "" ... etc.

[wizuriel]

I can't post the workbook, but I can make an example.

So I have 5 reports and 2 ID's. Problem is people want to find information on 1 sheet and not 5 reports. So what my code is doing is going through report 1 and matching ID A and ID B and outputting all ID's into the data sheet (already shown).

Users can also copy the column headers they want into the data sheet and when the code runs it finds which worksheet has that heading, and using the right ID pulls the desired data.

Now I have that all working, but it runs very slow.

edit: actual workbook can have something like 30k ID's and each worksheet 10-100 different header options

[wizuriel]

Well tried redoing it from vlookup 1 cell at a time, to just pasting the vlookup formula as an r1c1 formula. While it is a bit faster, it's still terribly slow . Don't think it is possible to really do this a better way though.

edit: Gah when I use formular1c1 it seems to still calculate the result despite using
Application.Calculation = xlCalculateManual

[GeneralDisarray]

Ok. I can't open that example, but without a workbook to see what you are doing I'm not sure I can help you

But, I can give you a couple of general ideas to ponder. You appear to be using application.vlookup in the code in place of a range.find method. Please see the code below. What this does is returns the Range where some value is present.

Looks like you may benefit from this technique, but I'm not really sure. I can tell you this code is lightning fast.

Please Login or Register  to view this content.

[wizuriel]

Tried the range find quickly and it seems slower than using VLookUp . Not sure why SkyDrive isn't working.

edit: I'm an idiot and just found the forum upload button.

edit 2: Uploaded a copy with my code. If you check in data first it shows what the page will initially look like. The button in setup will than run my code.

edit 3: moved upload to the first post

[wizuriel]

Going try one more bump for ideas.