Calculate the Date of an Item Based on the Date of a Similar Item in the Previous Month

Originally Posted by Tegglet

Given that I ONLY use [GoTo statements] in these circumstances can we agree to differ on this topic?

Of course, you are free to do this in whatever you think is the best way. I was a professional software engineer and engineering manager for 44 years and wrote production code in FORTRAN, PL/1, Ada, JOVIAL, C, C++, and Java, and I have never written a single GoTo statement in production code. Just my 2¢.

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

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.

In VBA, there are two best practices for dealing with sheet references. These are my opinions, but the opinions of a professional programmer who has been using VBA for 17 years.

1. Write code in the module for the worksheet being referenced. All unqualified references (such as Range("F4:F49")) will default to that worksheet
2. If you are writing code in a standard module (like Module1) that is intended to reference specific sheets, then you should explicitly qualify all sheet references, rather than using Activate then expecting references to default to the active sheet. The only exception to this is if you are writing a general macro to be called from a button that always operates on whatever sheet is currently active as a result of user actions.

Therefore, change the above two lines to this one line to eliminate any uncertainty regarding defaults:

     
      Set SearchValue = Worksheets(PrevWorksheet).Range("F4:F49").Find(What:="Receipt", LookAt:=xlPart, SearchOrder:=xlByRows)	'Looks for the search criteria

I will wade through the rest of your post when more time makes itself available.