Consulting

Results 1 to 3 of 3

Thread: Best method to check range completion before running rest of program.

  1. #1

    Best method to check range completion before running rest of program.

    I need to run a check on the completion of a range of cells before the rest of the program is run. For example if the range has cells with entries is over 70% then the rest of the program runs. If below then it doesn't and I'll drop in a message box.

    I thought of creating a couple of dimensions called 'cellscomp' using the Count function and 'cellsempty' using the Countblank function. Then writing a formula in code to extract the dimesion values, calulate the result and then if over 70% bingo.

    Is that the best method or are there others. Any ideas on the code to recall the dimension values and run the claculation appreciated.

    I attach a spreadsheet the range of cells is grey and the calculation of the percentage is below the range.
    Attached Files Attached Files

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,503
    Location
    I'd do something easy

    Option Explicit
    Sub test()
        Dim rData As Range
        Dim PercentCompleted As Double
        Dim cntData As Long
        
        Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
        
        On Error Resume Next    '   in case NO cells have numbers
        cntData = rData.SpecialCells(xlCellTypeConstants, xlNumbers).Count
        cntData = cntData + rData.SpecialCells(xlCellTypeFormulas, xlNumbers).Count
        On Error GoTo 0
        
        PercentCompleted = cntData / rData.Cells.Count
        
        MsgBox Format(PercentCompleted, "0.0%")
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    check on the completion of a range of cells before the rest of the program is run
    Algorithm:
    If CountA(Range) / Range.Count >= .7 Then
    Run rest of program
    Else
    Show Message box
    Please take the time to read the Forum FAQ

Posting Permissions

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