+ Reply to Thread
Results 1 to 8 of 8

Code To Find and Offset works on first sheet but won't loop to other sheets

  1. 03-05-2009, 03:57 PM #1
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Code To Find and Offset works on first sheet but won't loop to other sheets

    I have this code attached to a button on the first sheet of a workbook with hundreds of sheets. it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet. Please Help, 


    Please Login or Register  to view this content.


    Thanks

    Mark
    Last edited by mgaworecki; 03-10-2009 at 03:02 PM.
    Reply With QuoteRegister To Reply
  2. 03-05-2009, 03:59 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Hello Mark,

    You need to add the Wks variable as a prefix to .UsedRange.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 03-05-2009, 04:08 PM #3
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Leith,

    Thanks, but when I try that I get and error "Object Variable or With Block variable not set"
    Attached Files Attached Files
    Last edited by mgaworecki; 03-05-2009 at 04:13 PM.
    Reply With QuoteRegister To Reply
  4. 03-05-2009, 04:15 PM #4
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Leith,

    Never mind, it does work. It seems I have to save the workbook first. I was using a test workbook and I hadn't named or saved it yet. After I did, it seem to have worked great.


    Thanks a lot,

    Mark
    Reply With QuoteRegister To Reply
  5. 03-05-2009, 04:29 PM #5
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Ok, now, when I transfered the code into my main workbook I am getting that error again. I tried saving it, closing and reopening it and I still get it. Any suggestions?

    thanks,

    mark
    Reply With QuoteRegister To Reply
  6. 03-05-2009, 05:36 PM #6
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Hello mark,

    It was failing because there was no value "SAY:" on Sheet3. I corrected the macro to avoid that problem. Here is the working code...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-10-2009, 09:09 AM #7
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    Thanks, I'll try it today... sorry for the delay, I've been out of town.
    Reply With QuoteRegister To Reply
  8. 03-10-2009, 03:02 PM #8
    mgaworecki
    mgaworecki is offline
    Forum Contributor
    Join Date
    12-13-2007
    Location
    Central New York
    MS-Off Ver
    Home: 2007, Office: 2010
    Posts
    158

    Re: Code To Find and Offset works on first sheet but won't loop to other sheets

    That new code works great, although slow, almost 9 minutes (due to about 400 sheets).
    I also added a few lines to allow for multiple "SAY:"'s on a sheet

    Final code is this:

    Please Login or Register  to view this content.

    Thanks a lot for all the help.

    Mark
    Last edited by mgaworecki; 03-10-2009 at 03:04 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1