Hello,
I have some VBA code that searches keywords through a worksheet and deletes the line where the keyword is found. It does this through a loop. Since this is a lengthy process i have created a progress bar. The thing is, the progress bar works, but i have sort of cheated to make it work. In the part:
UserForm1.ProgressBar1.Value = (progress / 350) * 100
I have divided by 350, but i have merely found this number by trial and error. With this number the progress bar stops a 100% so this is just a temporary fix. I was wondering how i can count the number of times the loop runs so i can put the proper number instead of 350.
BTW i found most of this code on the internet.
Any ideas??
Sub MainScreen()
Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
Dim progress, total_rows As Variant
progress = 0
'show form
UserForm1.Show
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Set sh = ActiveSheet
Set myRng = sh.Range("A3:M1000")
myStrings = Array("#DIV/0!", "100.0%", "N/A", "finance")
With sh
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'We will search the values in MyRng in this example
With myRng
For I = LBound(myStrings) To UBound(myStrings)
Do
Set FoundCell = myRng.Find(What:=myStrings(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
'progress bar
progress = progress + 1
UserForm1.ProgressBar1.Value = (progress / 350) * 100
UserForm1.Label1.Caption = CInt((progress / 350) * 100) & "%"
DoEvents
Loop
Next I
End With
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
'bring progress bar to the end
UserForm1.Label1.Caption = "100%"
UserForm1.ProgressBar1.Value = 100
End Sub
Bookmarks