In my workbook, when I change the worksheet that this function is used on, or use F9, the macro generates the desired value. When I am on another worksheet in the workbook and make a change, the macro recalculates the values on the worksheet using this function to a zero. And, when I save the workbook, or open it, the recalculation resets all cells using the function zero as well. If I make the worksheet that uses this function active, and press F9, the macro calculates to the values desired and re-populates the cells on the active worksheet and elsewhere in the workbook with the values as desired.
I don't understand why this function is working this way. I need to always recalculate to its value and not reset to zero. Assistance is greatly 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 / 2
money = count
ReDim aMoneyValues(money)
'fill the money array with money. 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 + 2
counter = counter + 1
Next i
'evaluate the money array. Reset the counter i.
i = 0
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
