1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Problems with Ctrl + Find in a macro

Discussion in 'Business Applications' started by PincivMa, Jul 14, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    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
     

    Attached Files:

  2. OBP

    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.
     
  3. PincivMa

    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
     
  4. OBP

    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. :)
     

    Attached Files:

  5. PincivMa

    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
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/483171

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice