Vlookup is slow, loops are too slow

[bat18]

I am trying to record a macro which does a Vlookup on between 12,000 and 18,000 cells over another worksheet containing about 900 entries. At the moment the process is terribly slow, i simply typed in the formula and copied it down to cell 18000. This was very time consuming so I then decided to do a Do and then Loop Until but that was much much slower.

Is there a quicker way around all this??

 

[Jon von der Heyden]

Are you putting it in as a formula? Maybe switch to manual calculation at the start of the code, and then back to automatic at the end:

Application.Calculation = xlManual
'
'rest of code
'
Application.Calculation = xlAutomatic

It may be useful to see the code that you have tested this with.

 

[bat18]

Cheers

What does the code you've written actually mean?

The vlookup I'm using is below

=vlookup(c2, 'file name', 2, false)

 

[Jon von der Heyden]

What does the code you've written actually mean?

It's like going to Excel Options and choosing manual calculation from the Calculation tab. If you are looping then it avoids lengthy recalc between interations.