Counting calculated formulas in a Column
 

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(D261/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.

Counting calculated formulas in a Column
 

Hello,

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

Use HasFormula ...

Regards,
Bernd

Counting calculated formulas in a Column
 

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




"Brand" wrote in message
...
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(D261/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.

Counting calculated formulas in a Column
 

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)




"JLGWhiz" wrote in message
...
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




"Brand" wrote in message
...
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(D261/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.