+ Reply to Thread
Results 1 to 5 of 5

Looping through Worksheet and Workbooks in a directory

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    90

    Looping through Worksheet and Workbooks in a directory

    Hello everyone,

    I am attempting to run a routine that will clear certain cells given the appearance of text in a cell. The code itself runs fine for single worksheets that the words "Unit Cost" appears. However, in an attempt to have it run through all worksheets in a workbook, I get an error "Run-Time error '91': Object Variable or With block variable not set" once it gets to a worksheet that the phrase "Unit Cost" does not exist, and will not continue.

    What can I add/modify to the code below in order for it to ignore worksheets where that phrase does not appear, and continue on with additional worksheets (and additional workbooks in the specified directory)?

    My end goal is for the routine to run through all of the workbooks in the directory and update/save with the newly cleared cells.

    Please Login or Register  to view this content.
    Last edited by pynergee; 05-28-2013 at 02:32 PM. Reason: Gyclone assisted

  2. #2
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Looping through Worksheet and Workbooks in a directory

    You can wrap your code with some error handling:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Looping through Worksheet and Workbooks in a directory

    gyclone,

    That worked for the workbook that was open, except that it sent it into an infinite loop. Do you know a way to have it terminate after the last worksheet, and then continue to the next file in the directory?

    Thanks,
    Marc

    EDIT:

    My code to try to run through the directory was what was causing the loop error. Can you see any error in my current code which would cause it to loop through the worksheet and not the directory?
    Last edited by pynergee; 05-28-2013 at 01:41 PM. Reason: Noticed something

  4. #4
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Looping through Worksheet and Workbooks in a directory

    You got an infinite loop because you assigned a static value to StrFile and then told your macro to loop until StrFile had no value. Check out the below; one function to get the list of files, one sub to carry out your actions on each of the files in the list.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-28-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Looping through Worksheet and Workbooks in a directory

    I understand now, this functioned perfectly now (after adding a .save and .close so 1000 windows weren't open at once).

    Thank you for your help, +reputation and switched to Resolved.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1