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.

Hello,

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

Use HasFormula ...

Regards,
Bernd

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" <(E-Mail Removed)> wrote in message
news:3366D0E3-8296-4672-9420-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:3366D0E3-8296-4672-9420-(E-Mail Removed)...
>> 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.
>
>