CODEWITHSUNDEEP
excelvba
araau11
araau11

Reputation: 94

for each to a loop?

i'm looking to get a progress bar for my macro. but unfortunately the progress bar that im after requires a loop, however my code is a for each? I was just trying to figure a way to get this to work

This is what I currently have:

Sub Check()
OptimizedMode True
 Dim FindString As String
    Dim rng As Range
    Dim a As Long

   a = ThisWorkbook.Worksheets("Find").Range("W2").End(xlDown).Row

    For Each cell In Sheets("Find").Range("W2:W" & a)
        FindString = cell.Value
        
        If Trim(FindString) <> "" Then
            With Sheets("Table").Range("A:A")
                Set rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not rng Is Nothing Then
                Sheets("Table").Visible = True
                    Application.GoTo rng, True
                Else
                    cell.Offset(0, 0).Font.Color = vbRed
                End If
            End With
        End If
    Next
    Application.GoTo reference:=Range("a1"), Scroll:=True
    Sheets("Table").Visible = False
    Sheets("Macro").Activate
OptimizedMode False
End Sub

but for the progress bar to work the guide says it has to be in a loop. Is this anyway to implement this together?

Sub LoopThroughRows()
Dim i As Long, lastrow As Long
Dim pctdone As Single
lastrow = Range("A" & Rows.Count).End(xlUp).Row

'(Step 1) Display your Progress Bar
ufProgress.LabelProgress.Width = 0
ufProgress.Show
For i = 1 To lastrow
'(Step 2) Periodically update progress bar
    pctdone = i / lastrow
    With ufProgress
        .LabelCaption.Caption = "Processing Row " & i & " of " & lastrow
        .LabelProgress.Width = pctdone * (.FrameProgress.Width)
    End With
    DoEvents
        '--------------------------------------
        'the rest of your macro goes below here
        '
        '
        '--------------------------------------
'(Step 3) Close the progress bar when you're done
    If i = lastrow Then Unload ufProgress
Next i
End Sub

Upvotes: 0

Views: 108

Answers (1)

Amiga500
Amiga500

Reputation: 1275

Well, you have a as the last row from:

a = ThisWorkbook.Worksheets("Find").Range("W2").End(xlDown).Row

and you are starting at row 2. So your total rows iterated is (a-1) - removing the omitted row 1.

You could then add a counter and iterate it for each loop and use it, i.e.

i = 0
For Each cell In Sheets("Find").Range("W2:W" & a)
   '--------------------
   'Your other code here
   '--------------------

    pctdone = i / (a-1)
    With ufProgress
        .LabelCaption.Caption = "Processing Row " & i & " of " & (a-1)
        .LabelProgress.Width = pctdone * (.FrameProgress.Width)
    End With

    i = i + 1
next

Bit fugly, but it'd work.

Upvotes: 1

Related Questions