Hi,
Sorry for not making it clear that there was a lot more to it. I thought that if I posted the whole thing with a full description I would be breaking Forum rules, or at least bending them out of shape!

In recapping I noticed once again your emphatic comments regarding GoTo statements.
I strongly discourage using GoTo like this. Instead, the logic you want to skip should be included in the If part above. GoTo has been considered undesirable by professional software developers for at least 50 years. This is the source of what we used to call "spaghetti code." There are very special situations where I would not object but this isn't one of them.
When I first became involved with writing code (believe it or not that really was about 50 years ago!) it was drummed into me that I should not exit a routine from the middle and any conditional statement checking for a terminating condition should go to the end of the routine if one was found. This is the only time I use GoTo statements as I agree with you that using them in other circumstances leads to code that is more like a maze or as you put it spaghetti. In my support the statement "On Error GoTo ErrHandler" seems to be used extensively and seems to me to use a GoTo statement in much the same way as I am. Given that I ONLY use them in these circumstances can we agree to differ on this topic?

I have been trying to find ways round the problem but hit a brick wall every time and it has been most frustrating; although I am broadening my VBA knowledge considerably.

The first issue was with the following code lines:
       Worksheets(PrevWorksheet).Activate	'Activates the previous worksheet, the one with the date of the previous months receipt, ready for the search
      
      Set SearchValue = Range("F4:F49").Find(What:="Receipt", LookAt:=xlPart, SearchOrder:=xlByRows)	'Looks for the search criteria
      If Not SearchValue Is Nothing Then	'Checks to see that something has been found
          Debug.Print SearchValue
          CellAddr = SearchValue.Address	'Obtains the cell address of the search result and allocates it to CellAddr
          Debug.Print CellAddr
          PrevReceiptDate = Range(CellAddr).Offset(0, -3).Value	'Obtains the date of the receipt in the active workbook.
          Debug.Print PrevReceiptDate
      End If                                                              'At this point we have the date of the last receipt
The first line seems to be ignored so the second line (SearchValue ) only looks in the current sheet i.e. the one from which the UDF is called and of course it returns "Nothing" resulting in CurrReceiptDate having the wrong value. I have tried both activating the sheet and selecting the sheet, neither seem to work. This code segment works perfectly when used in a sub.

Getting this to work would be the ideal solution.

Regarding the calling the PrevSheet() function I have deleted to workbook in which I was trying to use it and I cannot reproduce the problem at the moment but as far as I remember I had to use Application.WorksheetFunction.PrevSheet(CellRef) or similar.

The other problem was getting it to recognise the argument. I have tried several ways of entering the cell reference including one set of double quotes and two sets of double quotes all to no avail. I am afraid this is showing up my sketchy knowledge of VBA. The code segment can be inserted in the example workbook uploaded already (I created a separate module “CheckPrevSheet” in my copy for clarity).

All this function is required to do is get the value of the Cell reference used as an argument from the previous sheet as it does when called from a worksheet. It is not part of the original problem.
Function ChkPrevSheet()

Dim CellVal As Variant

If ActiveSheet.Index > 1 Then                                               'Checks for the first sheet in the workbook i.e Apr
    CellVal = PrevSheet(C25)
    Debug.Print CellVal
End If

End Function
The plan was to call PrevSheet from within a loop indexing the PrevSheet argument from $F5 to $F49. This was a dismal failure and I am not spending any more time on it although it would be useful to know how to enter the cell reference for future use.

I had more or less given up on finding a simple solution that can be used with the existing workbook and I have been following up on your suggestion of recreating the entire workbook from scratch using VBA. I don’t want it written for me as part of the reason for the exercise is to keep my brain active and it is certainly doing that! This I am treating as a long term project to be ready for next April. I am hoping John’s book will provide the help I will be needing.

I have been trying out various bits of code before trying to create a new workbook and it has been refreshing trying out things that act in a predictable manner! The most interesting thing so far is setting up the conditional formatting using VBA. Conditional formatting has been a thorn in my side for a number of years and two of the supernumerary worksheets are copies used to transfer the formats across when the conditional formatting creates numerous segmented ranges. A button to reset these ranges will be both quick and more reliable.

Thanking you once again for your continued interest and support.
Tegglet