Counting calculated formulas in a Column

[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

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.

 

[Bernd P]

Hello,

I suggest to use GET.CELL or my UDF GetCell:
http://sulprobil.com/html/get_cell.html

Use HasFormula ...

Regards,
Bernd

 

[JLGWhiz]

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

 

[JLGWhiz]

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)