Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
Counting cells with a calculated value. | New Users to Excel | |||
counting only results in a column of formulas | Excel Programming | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |