Results 1 to 3 of 3

Problem creating a progress bar

Threaded View

  1. #1
    Registered User
    Join Date
    08-27-2008
    Location
    Montreal, Canada
    Posts
    2

    Problem creating a progress bar

    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
    Last edited by VBA Noob; 08-27-2008 at 04:56 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Countif funtions problem
    By mike4545 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2007, 02:37 PM
  2. Cell function problem
    By itty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2007, 08:27 AM
  3. Complex nested array formula problem and challenge
    By PeterWilliams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2007, 10:34 PM
  4. Math problem in excel
    By sisterpete in forum Excel General
    Replies: 5
    Last Post: 04-15-2007, 01:04 AM
  5. Replies: 2
    Last Post: 09-12-2006, 10:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1