PDA

View Full Version : Best method to check range completion before running rest of program.



LutonBarry
01-13-2018, 02:03 PM
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.

Paul_Hossler
01-14-2018, 08:28 AM
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

SamT
01-14-2018, 11:58 AM
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