Use Do While instead of For/Next

[cjeb456]

Hi all,

I found a piece of code online that help me to setup a progress bar on an excel macro that takes a while to run.
I have a small problem with it though - the code uses the For and Next statements and repeats this a set number of times to fill the progress bar.

My code uses the Do While statement as I cannot be sure how many times the code needs to be looped - depends on the users spreadsheet.

The code in question is...

[vba]
intMax = 100

For intIndex = 1 To intMax
sngPercent = intIndex / intMax
ProgressStyle1 sngPercent, chkPg1Value.Value
DoEvents

'My code goes here

Next
[/vba]

This will count from 1 to 100 and fills the progress bar on each loop

My code is...

[vba]
Do While Range("B" & lngCurrentRow).Text <> ""

'My code goes here

Loop
[/vba]

So the loop continues until Cell Bx is empty

How can i make the progress bar still work as it is an undetermined amount of loops

Many thanks

[tinyjack]

If you do not know how long a piece of string is, how can you know if you are near the end?

The thing I would do is set the progress bar up to act on a set number of records and the reset back to 0 everytime you reach that number, at tleast that way the user gets used to either how far through the bar it goes or how many times the bar fills.

TJ

[cjeb456]

Thanks for your reply. I was thinking perhaps before the main code starts it could do a quick count up of sections - how many times it will actually loop.

Just to make a bit a clearer my code sets the current row to 10 - it is then looking at column B
It checks B10 to make sure it is not empty
If it is not empty it will perform some copying and pasting and formatting
It then adds 10 to the current row and loops
So now it is lookign at B20 and does the same
Looks at B30 and so on until it finds itself on an empty cell at which point it will stop

Is there any code i could use to count how many times it will loop before it stops, then use that figure in the original progress bar code?

Thanks

[tinyjack]

You could use an empty loop that just records the final row number and then use that for your progress bar.

How many rows are you processing? I would not thought you would need to use a progress bar with the type of action that you are describing?

Could you show some of the code? Do you need to use copy and paste or could you just set cell values directly?

TJ

[Bob Phillips]

Presuably, if you have a loop, you either use a pre-determined counter, or you do loop until a condition is met.

So in your case you can look for the last non-blank row, divide by 10, and that should give you your PB value.