Recalculation issues with UDF

[goneps]

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:

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

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

[goneps]

This query now also cross-posted at Excel Forum under the same title (3060363)

[goneps]

Problem now seems to have been solved thanks to an Ozgrid MVP, who suggested a small change to the code.

[Ken Puls]

Hi goneps,

Glad you got your issue sorted out, and sorry for the frustration. I can't allow links or I get overwhelmed by spam. But you should have been able attach a workbook by using the "Go Advanced" feature when replying instead of the quick reply box. If you can't, that's certainly an issue...

[goneps]

Ken,

The message was to the effect that new members with fewer than 5 posts are not allowed to attach documents or include links. Since this will be my 5th post I won't be able to replicate the block, but I certainly did use the "Go Advanced" feature as I have on other forums using the same platform.

I can see why you don't allow links for new members, but the reason for barring attachments is not so obvious.

Cheers,

R.

[Ken Puls]

To be honest, I'm really stumped that you weren't able to upload an attachment. From day one I've worked to make sure that is possible, as it is a way to make sure that people don't need to post links.

I'm curious if you attempted to use a different method than what I'm expecting though. Here's what should work:

• Click Go Advanced
• Click Manage
• Follow the prompts

That should get the workbook into the upload queue... and it should work.

I'll test with a new account over the next few days to make sure it is.

[goneps]

Hullo Ken,

That's the procedure I followed. As mentioned I've successfully uploaded that file and others on other forums, so I know the procedure, and indeed I uploaded that file to the Excel Help forum at the same time. I'm pretty certain that the message forbade links AND attachments. However, you're now starting to shake my confidence, and my memory's by no means infallible. Might be worth testing it as you suggest, though, just to satisfy yourself that it's working as you expect. I've no desire to give you additional work, so I'll apologise in advance in case I'm mistaken.

R.