Run-time error '13' on Loop + Speed up macro

**GeorgY** · 03-04-2009, 02:10 PM

Can you guys tell me why I get a "Run-time error '13'?" on this line:
"If Cell = "#N/A" Then"

This is my code:

Please Login or Register  to view this content.

Much appreciate it!

**JBeaucaire** · 03-04-2009, 02:20 PM

Maybe this:

Please Login or Register  to view this content.

**GeorgY** · 03-04-2009, 03:00 PM

Thanks, that worked.

Now the problem is that I have about 45,000 rows. There are only 150-200 erros that would imply that about 150-200 rows would have to be copied. However, this seems to be taking forever, it copies about 1 line per minute.

Any way to speed it up?

**JBeaucaire** · 03-04-2009, 03:43 PM

If you posted a sheet, or a small sample of it, could fiddle with your code directly and see. Else just making general untested suggestions.

Perhaps not selecting cells will speed things up.

Please Login or Register  to view this content.

**davegugg** · 03-04-2009, 04:54 PM

Try turning off screen updating:
At the beginning of your code, after you name your variables put:

Please Login or Register  to view this content.

And at the end, before end sub put:

Please Login or Register  to view this content.

Dave

**GeorgY** · 03-05-2009, 01:58 AM

Originally Posted by JBeaucaire

If you posted a sheet, or a small sample of it, could fiddle with your code directly and see. Else just making general untested suggestions.

Perhaps not selecting cells will speed things up.

Please get the sheet from this URL, because I am having trouble attaching it via the forum:
http://bplusbsolutions.com/Book1.zip

It just takes it forever to run...almost as if it will never finish.

I zipped the file because there is lots of data on the spreadsheets.

**JBeaucaire** · 03-05-2009, 03:50 AM

Well, this code runs a little faster as it skips the "copy 45000 VLOOKUP formulas in place. But it still took over 10 minutes.

Please Login or Register  to view this content.

**GeorgY** · 03-05-2009, 11:19 AM

Originally Posted by JBeaucaire

Well, this code runs a little faster as it skips the "copy 45000 VLOOKUP formulas in place. But it still took over 10 minutes.

Thanks. It really does take a long time, for me it took over 15 minutes..perhaps I have a slower computer. It will have to do for now anyways.

If anyone can suggest a way to make this run quicker, please let me know.