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 Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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 Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    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
  •