Counting calculated formulas in a Column

  • Thread starter Thread starter Brand
  • Start date Start date
B

Brand

My spread sheet consists of approximately 550 rows and growing. Calculations
are calculated as data is entered by hand from left to right. For instance,
D5:M550 consists of of entered data, formulas that have been calculated, and
formulas that are equivalent to 0 due to not being calculated. I am looking
to be able to count the formulas that have automatically calculated themselfs
in a single column with out counting the values that have replace/deleted the
formulas as the values were entered by hand. This spread sheet calculates
due dates for documents as a previous document has been completed.


Formula= "=IF(D26>1/1/2000,D26+14,0)" Calculated date is in cell E26 is
1/14/2009 if the day which at some point was entered by hand in cell D26 is
1/1/2000, for instance 1/1/2009. Cell E27 is a hand entered date due to the
Click to expand...
document being completed. I need a formula that will count all of the
"calculated dates" in Column E, and not the calculated dates, plus the
entered dates. If more clarification is needed, please let me know.
 
See if this is what you want:

Sub countCalc()
Dim lr As Long, rng As Range, ws As Worksheet
Set ws = ActiveSheet 'Change to sheet name?
lr = ws.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = ws.Range("E2:E" & lr)
For Each c In rng
If c.HasFormula = True And c.Value <> 0 Then
Count = Count + 1
End If
Next
MsgBox "There are " & Count & " calculated cells."
End Sub
 
If you want to use the procedure like a worksheet function then use this
version:

Function HowMany(rng As Range)
Dim lr As Long, ws As Worksheet
Set ws = ActiveSheet 'Change to sheet name?
lr = ws.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = ws.Range("E2:E" & lr)
For Each c In rng
If c.HasFormula = True And c.Value <> 0 Then
Count = Count + 1
End If
Next
HowMany = Count
End Function

Put the code in the standard code module1.

To enter it on the worksheet:

HowMany(<enter range to count here>)

example: To count range E2 through E200
=HowMany(E2:E200)
 
Back
Top