Function will not calculate as desired reliably.

[DannyLee]

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.

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

 

[offthelip]

I think your function is not working quite the way you want it to. In detail it is accessing cells which are NOT within the range specified in rng range statement at the top.
You appear to be trying to load the values in your range into aMoneyValues however you are doing something very strange with this code:

For i = 0 To money - 1
    aMoneyValues(i) = Cells(row, counter).Value
    counter = counter + 1
Next i

You loop goes from 0 to the number of rows in your range, counter starts off with the column number of the range passed to the function, and then increments once for each of the loops.
So if you select range range D2 to D27 this means the cells you load are cells D2 to AE2. Is this really what you want.
There is a very easy way to load the values of the range passed to the function, I am not quite sure what you are trying to do with your function but try modifiying something like this:

Public Function LastMoneyValue(rng As Range) As Single
Dim aMoneyValues() As Variant
Dim vLastMoneyValue As Variant
Dim v As Variant



'Note ranges should be an even number. In this case 26 representing 13 months of the POP.


aMoneyValues = rng

'fill the money array. Increment the starting column by 1 the first time as the money is in the adjacent cell to the hours.


'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

 

[DannyLee]

Thank you. I got her fixed with this help.