Alternatives to using a for loop in cunjunction with vlookup

[Bobcat1]

I have sheets A, B, C the information in sheet A (contains about 15,000 rows) is constant, Sheet B is Dynamic (contains about 15,000 rows but is usually filtered to about 60-600 rows), and sheet C is where i wish to pool information from both sheets (will typically hold the same amount of rows as sheet B after its been filtered).

So once the data from sheet b has been pasted to sheet c, now I need sheet c to be populated with the data from sheet A. To do this i use a for loop along with a vlookup (baed on an ID# that is common in all sheets), the code looks something like this

for x = 1 to y (y being set to last row with data)

Please Login or Register  to view this content.

I know the formula isn't exact and the syntax is a bit off i'm kind of going of memory as I left the file at work, but I hope the gist of it. Ofcourse I need more than just one column from sheet A (its actually more like 6), and i just rinse and repeat to populate the rest of the columns. The formula I have works, it just take about 5 minutes to run, my question is if there is a better alternative to the for loop (as i think that is what slows everything down) also is the fact that i'm looping through and seting the formula for the current cell have anything to do with it? Like I said I'm just looking to reduce the time of this macro, I will try and put more info tomorrow from work if u need it thanks for the help.

[JieJenn]

Sounds like advanced filter for me.

http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

[Bobcat1]

went from 5 minutes to 17 seconds using these babies:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Now I'm having a problem with curcell which is dimed as a range.

so my code reads :

Please Login or Register  to view this content.

I keep getting a type mismatch error highlighting the first line in this code. Any suggestions?

[Bobcat1]

Never Mind I Fixed it, the vlookup was looking for an empty cell derp....

[Cutter]

@ Bobcat1

Based on your last post it seems that your issue has been resolved but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.