This function's behavior has me stumped. I want it to run and recalculate whenever a cell in rng changes. Sometimes it does, sometimes it doesn't. It depends on how I enter a new value in rng (copy/paste, keyboard, another UDF, etc.). The result is that the function isn't updating to the most recent value in rng reliably. It displays the last (old) value until I do something manually in one of the cells in rng to kick off the macro. So, I opted for Application.Volatile to have it recalc each time anything changes in the workbook. That's not working as hoped either. If any worksheet in the workbook changes, then the worksheet that uses this function sets itself to zero in all cells that use it. If I save the workbook, or on opening it, zeros as well. F9 has no affect unless I make the worksheet that uses the function active first and press F9, then the function calculates as expected showing the most recent value. Afterward, if I make an unrelated change anywhere else in the workbook, these cells set themselves back to zero on the worksheet where the function is used.

I need it to behave like a normal Excel function. That is not setting itself to zero, and reliably running to select the last cell value in rng when a cell's value changes from zero to non-zero (which doesn't happen reliably) and presenting that newest value in Rng that changed from zero to non-zero. Note that the range can have non-contiguous sets of values, with some of the values in between remaining zero. The range of cells doesn't change once set, only the cell values are updated over time. When this UDF runs, it provides the correct result. I can't seem to make this run reliably so that it is always presenting the most recent non-zero cell value (the UBound of the below array).

All help appreciated! Thank you.

I need it to behave like a normal Excel function. That is not setting itself to zero, and reliably running to select the last cell value in rng when a cell's value changes from zero to non-zero (which doesn't happen reliably) and presenting that newest value in Rng that changed from zero to non-zero. Note that the range can have non-contiguous sets of values, with some of the values in between remaining zero. The range of cells doesn't change once set, only the cell values are updated over time. When this UDF runs, it provides the correct result. I can't seem to make this run reliably so that it is always presenting the most recent non-zero cell value (the UBound of the below array).

All help appreciated! Thank you.

VBA Code:

```
Public Function LastMoneyValue(rng As Range) As Single
Dim v As Variant
Dim count As Integer
Dim i As Variant
Dim row As Long
Dim column As Long
Dim aMoneyValues() As Variant
Dim vLastMoneyValue As Variant
Dim money As Single
Dim counter As Long
Application.Volatile
count = rng.Cells.count
row = rng.row
column = rng.column
counter = column 'use counter to increment through the array. Hold the column variable constant.
'Note ranges should be an even number. In this case 26 representing 13 months of the POP.
money = count
ReDim aMoneyValues(money)
'fill the money array. Increment the starting column by 1 the first time as the money is in the adjacent cell to the hours.
For i = 0 To money - 1
aMoneyValues(i) = Cells(row, counter).value
counter = counter + 1
Next i
'evaluate the money array. Return the last cell in rng that has a non-zero value in it.
For i = LBound(aMoneyValues) To UBound(aMoneyValues)
v = aMoneyValues(i)
If v > 0 Or v < 0 Then
vLastMoneyValue = aMoneyValues(i)
End If
Next i
LastMoneyValue = vLastMoneyValue
End Function
```

Last edited by a moderator: