+ Reply to Thread
Results 1 to 5 of 5

Status Bar with For Each Stmt

  1. #1
    ExcelMonkey
    Guest

    Status Bar with For Each Stmt


    I usually use status bars with For Next loops as I always
    know how many loops will occur in my statement (i.e. For x
    = 1 to 100 tells me there will be 100 loops).

    Thus X/100 tells me my percentage completed.

    However I have a For Each loop that checks cells in a Used
    range of a worksheet. I am assuming that I can back out
    the number of colums and rows (c*r=#cells) within the
    UsedRange property to also build a status bar.

    For Each cell In sh.UsedRange

    Next

    Does anyone know how to do this? Thanks

  2. #2
    Simon Livings
    Guest

    Re: Status Bar with For Each Stmt

    Without wishing to be flippant, why don't you just increase a variable
    as a counter and use this instead. This is probably easier than trying
    to backsolve the cell number in the range. eg

    For Each cell in sh.UsedRange
    i=i+1
    Application.StatusBar=format(i/sh.usedrange.cells.count,"0%")
    next cell


  3. #3
    Bob Phillips
    Guest

    Re: Status Bar with For Each Stmt

    Use the UsedRange Count property to get a total of cells.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "ExcelMonkey" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I usually use status bars with For Next loops as I always
    > know how many loops will occur in my statement (i.e. For x
    > = 1 to 100 tells me there will be 100 loops).
    >
    > Thus X/100 tells me my percentage completed.
    >
    > However I have a For Each loop that checks cells in a Used
    > range of a worksheet. I am assuming that I can back out
    > the number of colums and rows (c*r=#cells) within the
    > UsedRange property to also build a status bar.
    >
    > For Each cell In sh.UsedRange
    >
    > Next
    >
    > Does anyone know how to do this? Thanks




  4. #4
    Simon Livings
    Guest

    Re: Status Bar with For Each Stmt

    Sorry - misunderstood which part of the code you were lacking.


  5. #5
    Bob Phillips
    Guest

    Re: Status Bar with For Each Stmt

    But you gave him the answer anyway :-)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Simon Livings" <[email protected]> wrote in message
    news:[email protected]...
    > Sorry - misunderstood which part of the code you were lacking.
    >




+ 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