goneps
New member
This query cross-posted at Ozgrid forum (t=173141), where the workbook sample is also available.
For a workbook that records and calculates vehicle fuel consumption I've combined two UDFs into one that serves both functions, depending upon the first argument passed by the formula. However, I've now run into recalculation problems. Two of the nine worksheets have formulas which call the function, but only the sheet that's active when the workbook is opened calculates correctly; the other has to be forced to recalculate with F9. If neither sheet is active when the workbook is opened then both show incorrect figures.
Having searched for answers I've incorporated Application.Volatile in the function but it makes little difference. The other suggestion was to pass all ranges needed for calculation as arguments, but how on earth could that be done?
The purpose of the UDF is to find the previous row when the tank was filled, when there might be a number of blank cells, ie. if the tank was only partially filled on previous occasions. Here's the code:
I've tried to attach a sample of the workbook. but am not allowed to since this is my first post on the forum, so apologies if this isn't as clear as it might be. As mentioned above the sample is available on the Ozgrid VBA forum.
With grateful thanks for any help that may be forthcoming.
Richard
For a workbook that records and calculates vehicle fuel consumption I've combined two UDFs into one that serves both functions, depending upon the first argument passed by the formula. However, I've now run into recalculation problems. Two of the nine worksheets have formulas which call the function, but only the sheet that's active when the workbook is opened calculates correctly; the other has to be forced to recalculate with F9. If neither sheet is active when the workbook is opened then both show incorrect figures.
Having searched for answers I've incorporated Application.Volatile in the function but it makes little difference. The other suggestion was to pass all ranges needed for calculation as arguments, but how on earth could that be done?
The purpose of the UDF is to find the previous row when the tank was filled, when there might be a number of blank cells, ie. if the tank was only partially filled on previous occasions. Here's the code:
Code:
Function Last(x As Integer, r As Range)
' Last(1,CellAddress) finds row for last fill
' Last(4,CellAddress) finds row for last 4 fills
' Use with INDEX function in formula
Application.Volatile
Dim i As Integer, n As Integer
If x = 1 Or x = 4 Then ' Check for correct parameter passed
i = r.Row
n = 0
Do
i = i - 1
If Range("B" & i).Value > 0 Then n = n + 1
Loop While n < x
Last = i
Else: Exit Function
End If
End Function
With grateful thanks for any help that may be forthcoming.
Richard