Progress Indicator help

Hello

I currently use the macro below & have (unsuccessfully) tried to add a progress indicator to it. The progress indicator I've been trying to squeeze in is this really good one from John Walkenbach.

At this stage i'm quite perplexed on how to do this. Please could someone help (& preferably add commentary so that i can learn)?

Thanks

Sub AppleDepartmentCode()

Dim Target As Range
Dim sContent
Dim c As Range

Set Target = Selection.SpecialCells(xlCellTypeVisible)

    Application.EnableEvents = False


        For Each c In Target    'for every cell in the selection

            If Not (c = "") Then    'if the cell in the selection is empty then move onto the next cell

                sContent = c.Value      ' store the current contents of the cell

                With c
                    ' put array formula in cell using the stored cell's contents
                    On Error Resume Next    ' trap any error
                    .FormulaArray = _
                        "=INDEX([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$2:$K$2,SMALL(IF([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$1:$K$40=""" & sContent & """,COLUMN([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$1:$K$40)),1))"
                    On Error GoTo 0
                    ' convert the formula to a value
                    .Value = .Value
                    ' if the value is an error type, restore the original contents
                    If Left(CStr(.Value), 5) = "Error" Then .Value = sContent
                End With

            End If

        Next c


Application.EnableEvents = True

End Sub

Hi 6StringJazzer

Apologies, i had meant to link to the John Walkenbach progress indicator. It can be found here: http://spreadsheetpage.com/index.php...ess_indicator/

PHP Code: 

Sub Main()
'   Inserts random numbers on the active worksheet'
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Cells.Clear
    Application.ScreenUpdating = False
    Counter = 1
    RowMax = 100
    ColMax = 25
    For r = 1 To RowMax
        For c = 1 To ColMax
            Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c
        PctDone = Counter / (RowMax * ColMax)
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
'       The DoEvents statement is responsible for the form updating'
        DoEvents
    Next r
    Unload UserForm1
End Sub 


The progress indicator bar i use doesn't have to be his but i found his explanations useful when following his code after downloading his workbook.

I think where i'm having difficulty is that his code inputs random numbers into the worksheet to demonstrate the code. Taking this out & substituting the code in my post is quite mind boggling to me. I still look at code in small chunks but looking at it holistically is hard!

Any pointers, suggestions or tips?

From my code i know i somehow want to count the total number in the selection:

PHP Code: 

For Each c In Target    'for every cell in the selection 


I also want to divide the following with the total number in the selection:

PHP Code: 

Next c 


I would then have the percentage out of 100% but how to put it into practice?

Originally Posted by 6StringJazzer

If you want to use his, then you can modify your code as shown:

Sub AppleDepartmentCode()

Dim Target As Range
Dim sContent
Dim c As Range
Dim PctDone As Double
Dim CellCount As Long

Set Target = Selection.SpecialCells(xlCellTypeVisible)

    Application.EnableEvents = False

        CellCount = 0
        For Each c In Target    'for every cell in the selection

            CellCount = CellCount + 1

            If Not (c = "") Then    'if the cell in the selection is empty then move onto the next cell

                sContent = c.Value      ' store the current contents of the cell

                With c
                    ' put array formula in cell using the stored cell's contents
                    On Error Resume Next    ' trap any error
                    .FormulaArray = _
                        "=INDEX([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$2:$K$2,SMALL(IF([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$1:$K$40=""" & sContent & """,COLUMN([PERSONAL.xlsb]ZZZZZZZAppleDepartmentNAME!$A$1:$K$40)),1))"
                    On Error GoTo 0
                    ' convert the formula to a value
                    .Value = .Value
                    ' if the value is an error type, restore the original contents
                    If Left(CStr(.Value), 5) = "Error" Then .Value = sContent
                End With

            End If

           PctDone = CellCount / Target.Count
           With UserForm1 
               .FrameProgress.Caption = Format(PctDone, "0%") 
               .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10) 
           End With 
           DoEvents

        Next c


Application.EnableEvents = True

End Sub

Wow PM6StringJazzer, that's awesome! I've finally had a chance to implement it & it works great. Thanks alot for your help

You're welcome, glad to help! Thanks for the rep and thanks for marking your thread SOLVED.