Progress Bar Help !!!! Please

[rabso]

Hello

I have a progress bar called Progressbar1. I would like to show the status of a loop as it takes a couple of minutes to run and I unsure how to do this. Can some one please help?

This is my code so far, thank you Richard Baker


Code:

Application.ScreenUpdating = False Sheets("Summary").SelectDim CurRow As IntegerCurRow = 1 For Each x In Range("A" & CurRow, Range("A" & CurRow).End(xlDown)) If x = "" Then Exit For 'Proof Number Column B x.Offset(0, 1).Formula = "=VLOOKUP(A" & CurRow & ", AX!A:I, 7, 0)" CurRow = CurRow + 1Next Application.ScreenUpdating = true</PRE>

 

[rabso]

Application.ScreenUpdating = False
 
Sheets("Summary").Select
Dim CurRow As Integer
CurRow = 1
    For Each x In Range("A" & CurRow, Range("A" & CurRow).End(xlDown))
    If x = "" Then Exit For
 
        'Proof Number Column B
        x.Offset(0, 1).Formula = "=VLOOKUP(A" & CurRow & ", AX!A:I, 7, 0)"
    CurRow = CurRow + 1
Next
 
Application.ScreenUpdating = true

 

[moonfish]

Code:

Application.ScreenUpdating = False Sheets("Summary").SelectDim CurRow As IntegerCurRow = 1 For Each x In Range("A" & CurRow, Range("A" & CurRow).End(xlDown)) If x = "" Then Exit For 'Proof Number Column B x.Offset(0, 1).Formula = "=VLOOKUP(A" & CurRow & ", AX!A:I, 7, 0)" CurRow = CurRow + 1Next Application.ScreenUpdating = true</PRE>

Can you please put this in a readable format? It seems your line breaks have gone missing. EDIT: whoops sorry, you did that while I was typing.

A general solution would be to have the progress bar display the number of processed loops in relation to the total number of loops.

 

[rabso]

Hi Moonfish

Thank you, its just below my initial request.

Thank you

 

[moonfish]

Application.ScreenUpdating = False
 
Sheets("Summary").Select
Dim CurRow As Integer
Dim MaxRow As Integer
CurRow = 1
MaxRow = ActiveSheet.UsedRange.Rows.Count
For Each x In Range("A" & CurRow, Range("A" & CurRow).End(xlDown))
    If x = "" Then Exit For
 
        'Proof Number Column B
        x.Offset(0, 1).Formula = "=VLOOKUP(A" & CurRow & ", AX!A:I, 7, 0)"
    CurRow = CurRow + 1
Next
 
Application.ScreenUpdating = true

For the progress bar, use CurRow for progress and MaxRow for the maximum. I'm not sure what your progress bar code looks like but if you wrote it yourself you should be able to implement these numbers.

 

[baitmaster]

use Application.Calculation = xlCalculationManual at start of routine, and Application.Calculation = xlCalculationAutomatic at end to speed your code up

Writing a load of VLOOKUPS automatically is probably the main reason your code is slow, because the sheet will recaculate between each

You can use application.statusbar = format([your formula here],"0.00%") to report easily without using a progress bar. Use application.statusbar = FALSE when done

 

[rabso]

Hi BaitMaster

Firstly sorry about my late reply and duplicated post. I have been unable to access this site and posts and replys were not being recieved.

The calculation protocol worked a treat. I couldnt figure out how to have a dynmic progress bar but the query sped up from 2 mins to 2 seconds

Thank you so much.