Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA Formula Fails to Execute
The procedure below increments a counter (StatusCount) based on the value
contained in range cells. The procedure executes properly when the first range cell value is changed but will not automatically execute again. Any assistance would be appreciated. Function StatusCount(Status) For Each cell In Worksheets("Sheet1").range("ILStatusRange").Cells If cell.Value = Status Then StatusCount = StatusCount + 1 End If Next End Function Function is shown in cell as: =StatusCount("O") where "O" is one of the six values that occur in the range. |
#2
|
|||
|
|||
Since you embedded the range to check inside the code, excel doesn't know when
to recalculate this function. It would be better to pass that status code and the range to the function. Then if either changes, the function will recalculate: Function StatusCount(Status as string, rng as range) as long dim cell as range For Each cell In rng.Cells If cell.Value = Status Then StatusCount = StatusCount + 1 End If Next cell End Function And you could call it like: =statuscount("O",sheet1!Ilstatusrange) ========= But excel has a function that looks a lot like your function. Take a look at =countif() =countif(sheet1!ilstatusrange,"0") or =countif(ilstatusrange,"0") if you're on sheet1 or ilstatusrange is a workbook level name. Patrice Stewart wrote: The procedure below increments a counter (StatusCount) based on the value contained in range cells. The procedure executes properly when the first range cell value is changed but will not automatically execute again. Any assistance would be appreciated. Function StatusCount(Status) For Each cell In Worksheets("Sheet1").range("ILStatusRange").Cells If cell.Value = Status Then StatusCount = StatusCount + 1 End If Next End Function Function is shown in cell as: =StatusCount("O") where "O" is one of the six values that occur in the range. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
Formula or not? | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |