Run time - % complete
 

Hi. I have a procedure that takes about 3 minutes to run on my machine, but
takes about 5 minutes on a "standard issue" machine. The code loops through
10 sheets. Is there a way to incorporate code that provides the end user
with a % complete indicator? Something (maybe a modeless message box, or in
the status indicator bar) that would update after each sheet loop in
increments of 10% (simple math for 10 sheets). Thanks! My code looks like
this:

Set shtarray = Sheets(Array("Sheet1", "Sheet1",....., "Sheet10"))

For Each sh In shtarray

With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
..Rows(2).Copy .Rows("5:" & x)
End With

Next

Run time - % complete
 

Yeah, just add a counter...

Dim cnt as Long
Dim cntTotal as Long

cntTotal = 10

For Each sh In shtarray

With sh
cnt = cnt + 1
Application.StatusBar = Format(cnt/cntTotal,"Percent") & " Complete"
x = .Cells(Rows.Count, "B").End(xlUp).Row
..Rows(2).Copy .Rows("5:" & x)
End With

Next

"Steph" wrote:

Hi. I have a procedure that takes about 3 minutes to run on my machine, but
takes about 5 minutes on a "standard issue" machine. The code loops through
10 sheets. Is there a way to incorporate code that provides the end user
with a % complete indicator? Something (maybe a modeless message box, or in
the status indicator bar) that would update after each sheet loop in
increments of 10% (simple math for 10 sheets). Thanks! My code looks like
this:

Set shtarray = Sheets(Array("Sheet1", "Sheet1",....., "Sheet10"))

For Each sh In shtarray

With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
..Rows(2).Copy .Rows("5:" & x)
End With

Next

Run time - % complete
 

Steph

Here's some links to fancy progress bars (at the end)

http://www.dicks-blog.com/excel/2004...gress_bar.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Steph" wrote in message
...
Hi. I have a procedure that takes about 3 minutes to run on my machine,
but
takes about 5 minutes on a "standard issue" machine. The code loops
through
10 sheets. Is there a way to incorporate code that provides the end user
with a % complete indicator? Something (maybe a modeless message box, or
in
the status indicator bar) that would update after each sheet loop in
increments of 10% (simple math for 10 sheets). Thanks! My code looks
like
this:

Set shtarray = Sheets(Array("Sheet1", "Sheet1",....., "Sheet10"))

For Each sh In shtarray

With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
.Rows(2).Copy .Rows("5:" & x)
End With

Next

Run time - % complete
 

I have some progress bar examples on my website.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Steph" wrote in message
...
Hi. I have a procedure that takes about 3 minutes to run on my machine,
but
takes about 5 minutes on a "standard issue" machine. The code loops
through
10 sheets. Is there a way to incorporate code that provides the end user
with a % complete indicator? Something (maybe a modeless message box, or
in
the status indicator bar) that would update after each sheet loop in
increments of 10% (simple math for 10 sheets). Thanks! My code looks
like
this:

Set shtarray = Sheets(Array("Sheet1", "Sheet1",....., "Sheet10"))

For Each sh In shtarray

With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
.Rows(2).Copy .Rows("5:" & x)
End With

Next