Progress Bar Help !!!! Please

R

rabso

New Member
Joined
Oct 21, 2010
Messages
36
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>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Code: 
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
 
Upvote 0
rabso said:
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>
Click to expand...
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.
 
Upvote 0
Code: 
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
VBA display progress (e.g. in status bar) for huge iteration
Replies
3
Views
4K
Akuini
Akuini
R
  • Question Question
VBA Progress bar
Replies
4
Views
920
Rhemo
R
J
  • Solved
VBA to Convert Cell Values to Hyperlinks
Replies
2
Views
2K
JohanGduToit
J
G
  • Question Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G
B
  • Question Question
Progress Bar
Replies
6
Views
624
Braunschweiger
B

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top