Problems with Ctrl + Find in a macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

PincivMa

Thread Starter
Joined
Mar 13, 2004
Messages
378
Hi There

I have a problem with the attached spread sheet. I am trying to use the Ctrl + Find in a macro and it gives me an error if it does not find what it is looking for. Using the "On Error Resume Next" alleviates the error message but it screws up the logic of my macro. T

Work with the attached sheet and you will see what I mean. Using the "On Error Resume Next" line, the macro just keeps on copying the "a" over and over until the macro ends. This is not the intention of the macro. When the Macro does not find the number 1 in Sheet 1, it should go to the next cell down in Sheet 2 etc until it reaches a blank cell and then the macro stops.

Any help would be appreciated. The attached spreadsheet is an example only. I need to use the Ctrl + Find in the macro since it makes the macro run faster due to over 1000 records.

I could use the Do....Loop Until the ActivCell = Range("C1") and then do the copy paste but this method takes forever to go through all the records until it finds the right ones.

Any help would be greatly appreciated.

I hope I attached the Excel workbook properly.

Mario
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
How long does it take to test 1000+ records, it shouldn't be more than a second or two?
I restricted your search area to a range wher the data is stored rather than the whole column and it works fine.
 

PincivMa

Thread Starter
Joined
Mar 13, 2004
Messages
378
Hi OBP

If you ran my macro in the attached file, you would see that I would get an error before the macro is finished. How do you avoid the error and make tha macro work properly.

I'm not sure what you ment by the statement "I restricted your search area to a range where the data is stored rather than the whole column and it works fine." ??

Mario
 

OBP

Joined
Mar 8, 2005
Messages
19,895
When I ran your macro again even with the Range limited I still got your error. :eek:
So I have put an error trap in for err number 91 which is the find not finding and added a "resumehere" line name. So when you get an err 91 it resumes on Sheet 2 and gets the next number to find. I have added another "g" on the bottom of your list to show that it is working correctly.
I hope this is what you wanted. :)
 

Attachments

PincivMa

Thread Starter
Joined
Mar 13, 2004
Messages
378
Hi OBP

Thanks a million. That is exactly what I wanted, an error handling routine. I can now use it in my macro and see if it works properly.

Mario
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Staff online

Members online

Top