Progress Bar

[K]

Hi all, I have macro below which works fine but the only thing that
progress bar don’t complete it self. I know I need to do something on
line
Sheets("Search").ProgressBar1 = SrchShRowCount
I did few experiments but had no success. Please can any friend can
help

Macro*************************************************
Sub srchtransvire()
LstCel = Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Row
bx = Sheets("Search").TextBox1.Value
DataShRowCount = 2
SrchShRowCount = 9
Srchnum = bx
With Sheets("DATA")
Sheets("Search").ProgressBar1.Visible = True
Sheets("Search").ProgressBar1.Appearance = cc3D
Sheets("Search").ProgressBar1.Appearance = ccFlat
Do While .Range("A" & DataShRowCount) <> ""
If .Range("A" & DataShRowCount) Like "*" & Srchnum & "*" Then
Set CopyRange = .Range("A" & DataShRowCount & ":I" & DataShRowCount)
CopyRange.Copy Destination:=Sheets("Search").Range("A" &
SrchShRowCount)
SrchShRowCount = SrchShRowCount + 1
Sheets("Search").ProgressBar1 = SrchShRowCount
DoEvents
End If
DataShRowCount = DataShRowCount + 1
Loop
End With
Sheets("Search").ProgressBar1.Visible = False
End Sub
**********************************************************

 

[FSt1]

hi
I think your progress bar is measuring the wrong thing.
your update is inside the if clause meaning the progress meter only updates
IF something was copied and is based on SrchShRowCount which is the number of
items copied not the number of rows in DataShRowCount that were processed
(copied or not).
also you do find the last row but i cant see were you use that. the progress
meter should be measureing how many rows were process (DataShRowCount)
compared against the total number of rows (lstrow) as a percent complete.
i would move the meter update outside the if clause and base it on total
rows processed not total rows copied.

also see these sites..............
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/
http://support.microsoft.com/kb/211736

regards
FSt1