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

I have an Excel 2013 spreadsheet comprising twelve sheets named for the corresponding months starting with Apr (Apr, May, Jun. Jul, Aug etc). With the exception of Apr (the first sheet) each sheet needs to contain the following formula:

Formula:

Please Login or Register  to view this content.

It contains two references to data in the previous month (sheet).

The first reference finds the previous month's name using the formula segment

Formula:

Please Login or Register  to view this content.

where $B$4 contains the date of the first of the current month. So far this has worked as expected.

I need to do the same thing for the second occurrence of the sheet name.

I have tried a straight substitution as follows:

Formula:

Please Login or Register  to view this content.

which is rejected out of hand.

I have tried various forms of concatenation, wrapping various bits in double or single quotes and other variations, none of which work. They are either rejected completely or give #REF or #VALUE errors.

This formula, or one similar, is used several times on each of the twelve sheets and manually entering the sheet name is tedious beyond measure and a plentiful source of errors. Once it is finished I will be using the workbook as a template so there is a considerable saving in solving the problem.

Can anyone help please?

Assuming that the expected result is "June Target Item 14", try:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Lord willing, we will still be here when you get ready to pick this back up, so don't worry about that.
If you have some time while waiting to be discharged you might consider a couple of things.
1. Would it be feasible to put the result of the formula from post #5 in a cell and let your real world formula reference that cell in the two or three places rather than inserting the formula from post #5 two or three times?
2. If not, is there a way to post a small desensitized version of your real world workbook, so that we could see if there is a better solution?
All the best.

Just returning home, so although post #8 was made almost a week ago, I am just now looking at it.

I am trying to calculate the dates for payments received every four weeks (on Fridays) using an initial seed date on the April (first) worksheet.

It seems to me that the following should yield the desired results:
So that the list of dates start with a Friday, paste the following into cell C4 (April Sheet): =B4+7-WEEKDAY(B4+1)
In cells C5 and down: =SUM(C4,28)

For a number of reasons I enter the day of the month (1, 2, 3 etc) in one column and calculate the actual date from that in another column. This means that the different number of days in each month is not handled automatically.

It would probably help if we could see a sample as I don't really understand what this means.
Let us know if you have any questions.

I have given up trying to do this using formulae, it works alright until there are two receipts in one calendar month. I overcame the problem pro tem by entering the second date manually. I am now trying to do the job properly using VBA.

To recap:
I have a complex cash flow forecast spreadsheet in which I want to add entries for four weekly receipts. It comprises one worksheet for each month starting with April (Apr) through to the following March (Mar). For various reasons I enter the day number in one column (C) which acts as a seed for generating the date in the preceding column (B). The sample worksheet attached just has worksheets for April and May; subsequent months are just a repeat of May until there are two receipts in one month. That will be dealt with once I can get this bit working.
I am trying to write a function to automatically generate the date of the receipt for the current month based that for the previous month. The date for April is entered manually based on the last receipt in the previous financial year.

My code works as I want it to when I run it from the code window (placing the correct day number in the active cell) but I cannot get it to work by entering “=FindReceiptDate()” or “=FindReceiptDate(parameter)” in the relevant cell.
I suspect I need to return the value of the “ReceiptDate” variable but cannot find how to do that.
Can anyone help please?

The code is as follows (a check for the correct active column (C) will be added later):

Please Login or Register  to view this content.

Originally Posted by Tegglet

I have given up trying to do this using formulae, it works alright until there are two receipts in one calendar month. I overcame the problem pro tem by entering the second date manually. I am now trying to do the job properly using VBA.

Moved from Excel Formulas & Functions

Thank you for you friendly and helpful reply NOT! A little toleration in your response would have been nice.

What do you mean by

Moved from Excel Formulas & Functions

? Where has it been moved to or has it been removed altogether?

I had major problems navigating the rather convoluted process of entering code and attaching the example file and made a number of attempts at posting my update; losing my content several times. I even posted a query with the webmaster. The forum format and navigation, not to mention some moderators, is not very friendly to occasional users.

The final post was made by accident and I was so relieved at getting something to stick that actually had retained the attached file that I failed to notice that the code was not enclosed by code tags. Here it is again, enclosed by tags:

Please Login or Register  to view this content.

Edit
Further inspection suggests that I had enclosed the code in code tags. In this post, in which the code definitely was enclosed in tags, the code is displayed in a panel with a grey background and with a "Copy to clipboard" icon identical to my previous post. On what have you based your criticism?

I asked the moderator to move the thread to a forum on this site where those contributors whom know more about VBA than I (notice at the top of the page it is now in the Excel Programming / VBA / Macros forum) would have a better opportunity to see your post and respond.
If there is any hostility about moving the thread, please direct it towards me.
I hope that someone will be able to help and that you have a blessed day.

Originally Posted by Tegglet

Thank you for you friendly and helpful reply NOT! A little toleration in your response would have been nice.

What do you mean by? Where has it been moved to or has it been removed altogether?

You said you decided you needed a VBA solution, so I moved the whole thread to the VBA subforum, where it will get more attention from people with specific knowledge of VBA. Which I think is actually pretty friendly and helpful. Nothing has been removed. This is not a rule violation. You were not penalized in any way. The thread is still reachable at the same link.

Edit
Further inspection suggests that I had enclosed the code in code tags. In this post, in which the code definitely was enclosed in tags, the code is displayed in a panel with a grey background and with a "Copy to clipboard" icon identical to my previous post. On what have you based your criticism?

Not sure what you are getting at here. I see no comments anywhere in the thread about lacking code tags.

I have found that people prefer short, to-the-point notes on moderation actions without a long explanation clogging up the thread. It is common to move threads to different subforums to get the appropriate exposure. My apologies if you found it overly terse.

Originally Posted by Tegglet

My comments regarding code tags referred to the following:

That is my signature. It was not aimed at you, or any individual. It appears every time I post to any thread. Anyone can create a signature by editing their profile. My signature first identifies me, then has two reminders. The first reminder is to read the rules because so many people join the board and then start posting without reading the rules. The second is information about code tags, something easy to do but a frequent rule violation.

I was experiencing significant difficulty in navigating the site, especially regarding attaching the sample file, and lost my text several times and had to retype it, perhaps you could use your influence as a moderator on the powers that be to make the process easier to follow. I did send a message using the contact form but I have yet to receive a reply

The only problem I am aware of regarding attachments is that the paper clip icon doesn't do anything, so you have to go through different steps. The yellow banner at the top of every page explains how to do this. We are very much open to suggestions about how to make the process easier to follow, so feel free to PM me your ideas.

The only time I have seen text lost is every once in a while there is a database bug that causes text to disappear if you attempt to edit an existing post that has already been posted. I don't know if this is what you experienced. In such as case when you click Edit Post, the window to edit the text becomes blank. The text is not actually lost; you can hit the Back button to return to the original post without losing anything, but you can't edit it. This is rare. Moderators and Administrators are all volunteers who try to keep the forum running smoothly but we are not employees and no direct leverage with the owners and technical team. All we can is to submit tickets and send requests. I have reported this but it still occurs occasionally.

The attached spreadsheet is now current and the previous one should be discarded. The following code matches the attached spreadsheet.

I have also been getting an intermittent error when the "End Function" statement in the code is ignored and a UDF {PrevSheet()} in a user add-in gets called. This results in an infinite loop which can only be terminated using Task Manager.
NB: Having checked the file uploaded I see that the UDF in the add-in did not travelled with it. I have therefore entered the date manually.

All I want to do is call a user defined function from a cell that does the following:
1) Looks in the previous month for the receipt.
2) Gets the day the receipt was received.
3) Adds 28 days to that day then subtracts the number of days in the previous month to get the day the receipt will be received in the current month.
4) Returns the result and displays it in the cell.
5) From this the date of the receipt in the current month is obtained.

I use the day in a separate column as it is easier to manipulate entries when adjusting and sorting entries. It is also less likely to screw up the conditional formatting used in the main spreadsheet.

It seems to me that the problem lies in how to return the value using the function.

I would appreciate it if someone would be kind enough to review my code and method of implementation give me some idea of where it is going wrong.

Please Login or Register  to view this content.

Originally Posted by Tegglet

the UDF in the add-in did not travelled with it.

An add-in is specific to your Excel installation. It is not linked into any of your Excel files. It will work only on your machine, or if other users have the same add-in installed.

From the notes in your file:

When I run the function with the parameter "CurrReceiptDate" from the worksheet I get Run-time error " '91': Object variable or With block variable not set".

The definition of your function does not have a parameter, so you cannot call it with a parameter. Also, to debug UDFs it helps to write a Sub that calls it so you can control the execution better than when it is called from a worksheet.

I need to spend some time figuring out how your file works but I'll have more input as I go.

There are significant problems in the UDF design. As a result it is difficult to determine your intent by reading the code, and the comments do not give a lot of context for what this file is all about. Here is my initial analysis:

This function is being called from worksheet cells, however, the code is referring to ActiveCell. This is not the cell that contains the call to the function, it is whatever cell is active. Therefore the function will always return the same value in all cells wherever it is used. I doubt that is what you are trying to do, although I still can't figure out what you are trying to do.

Please Login or Register  to view this content.

This will always refer to whatever cell the user has currently selected, regardless of what cell contains the formula.

A UDF called from a worksheet may not cause a change in a cell other than the cell it was called from. Therefore the following line will cause an error 91:

Please Login or Register  to view this content.

When I run the function code from the code module (F5) I get the correct result.

Yes, that is because a function called from a Sub or run by the debugger (F5) does not have the same restrictions as when it is called from a worksheet.

The return value FindReceiptDate for your function is never set, so it will always return 0 (if it does not produce an error). The line that sets the return value is commented out:

Please Login or Register  to view this content.

This code uses Integer variables to store dates. However, Excel dates exceed the Integer value limits. You must use Long for dates.

It is almost always better to refer directly to sheets and cells. Instead of doing this, this code Selects a sheet and cell, and then depends on ActiveCell to refer to it.

Please Login or Register  to view this content.

How does the following line of code determine a date? It refers to column C, which does not contain a date. It contains the result of this function, so it seems this is circular logic.

Please Login or Register  to view this content.

Why is the following code commented out?

Please Login or Register  to view this content.

I would also restructure the logic to eliminate all GoTo statements. They are not a good programming practice and nearly always unnecessary.

Once I figure out what you are trying to do functionally, I will provide specific recommendations for revisions.

I still can't figure out what this is doing functionally. I get that it somehow tracks receipts but that's as far as I can get. It would help to have more complete sample data. Can you address the following:

1. Your worksheets only have a few rows of data, with many blank rows in between the populated rows.
2. Why are so many rows left blank?
3. What determines what rows are populated?
4. What determines what data is entered in the rows that do have data?
5. The only purpose of row 4 seems to be to contain the date for the first day of the month. Is that correct? What does BBF mean? If it is not part of your receipt data I would put it somewhere else on the sheet, although there is no real harm in it.

I am in the weeds on your code without understanding the actual application you are trying to develop. I read your description and still cannot relate the bare-bones file with what you are actually doing. The fact there are only two sheets is fine, but I just don't understand how your data works. If I can understand that, I expect I will start fresh instead of trying to modify existing code.

1) Your worksheets only have a few rows of data, with many blank rows in between the populated rows.

The real life spreadsheet comprises 34 worksheets packed with confidential financial data. I have tried to fillet a couple of sheets so they contain only data germane to the problem.


2) Why are so many rows left blank?

as question 1)

There is nothing here that explains why you just have a few populated rows scattered about and mostly blank rows.

3) What determines what rows are populated?

All rows are populated in sequence and contain the details of the expenditure or receipt for the date in column A.

If they are populated in sequence, it does not explain why only a few rows are populated with blank rows in between.

4) Column C is the day of the month from which the date in the form dd-mm-yyyy (column B) and the day in the form dddd (column A) are derived.

This is probably the point where my understanding falters, and is also what your question is all about. Why do you need a function to calculate this day? Why not just enter the actual day that the actual transaction occurs?

Column E sets the criteria for financial (from drop down list) . Column F contains the description of the item (entered). Columns M and N contain payments and receipts (entered) column O give the status i.e. R for reconciled (entered) and column P is the balance derived from M & N. BBF is Balance Brought Forward (from previous month)

5) The only purpose of row 4 seems to be to contain the date for the first day of the month. Is that correct?

Yes

What does BBF mean?

BBF is Balance Brought Forward (from previous month).

The code for PrevSheet() was lifted of the web many years ago and tweaked to suit. It does not work with Excel 2016 however, producing circular references for some reason. I did put it up on the forum but got no response.

It works fine for me in Excel 365 in your current file and also the file you posted in the other thread. The code is pretty straightforward and I don't see anything that would cause this. Not being a professional Excel consultant, I do not have any older versions of Excel to test with.

Please Login or Register  to view this content.

Range is not a data type nor keyword, it is a built-in class (Workbook and Worksheet are examples of the same thing). Keywords will be shown in blue (by default--you can change it).

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Similar comment here.

Please Login or Register  to view this content.

I explained this sort of thing in an earlier post. A Function that is called from a worksheet formula has one and only one purpose: To return a value. You may not use a function called from a worksheet to make any changes to any other cells. These are called side effects. That is why you get a #VALUE error.

Let me tell you how I would do this, now that I get a better picture of what your data looks like. I would use VBA to create each new Month sheet. I would create a hidden sheet that is the template for this month sheet, so the formatting (including conditional formatting) and fixed text is already in place. The VBA would set up the blank month sheet from the template, bringing the prior month's balance forward, and then prepopulate the events where you get the receipt every four weeks. To do this, it would need some anchor date where the first receipt occurred. I suppose it could retrieve this from a prior sheet. Then after you enter your daily data, you would re-sort to your heart's desire.

The one thing that looks like a problem to me is that on April 27 you have a paid amount of 52.00 but the Item is "Receipt". This entry will be found by your existing VBA code, because it searches for the word "Receipt" using xlPart as the search option. It would be better to search for the entire phrase "Receipt - Every four weeks" and use xlWhole.

For the sake of closure, here is how I would rewrite the code to eliminate the GoTo statements.

Please Login or Register  to view this content.

Here is a new solution.

I have added two sheets:

• Controls: Has one button to create the next month's sheet. Can be updated to include other buttons if needed, or this button could be moved somewhere else.
• Template: A template for a month sheet that has all formulas and formatting. This is a hidden sheet but could be visible if desired.

The button to add a sheet does this:

1. Copies the template to create a new sheet after the latest month
2. Renames the new sheet based on the month in the form Mmm
3. Finds a recurring receipt in the previous month and retrieves the date
4. Increments that date by 28 days and finds all occurrences in the new month (1 or 2 occurrences)

You will then key in your other transactions manually as needed, and sort as desired.

I cannot, however, understand why a UDF cannot find a value on another worksheet on which to base a calculation to obtain its return value? To me it seems an unreasonable restriction, after all my public UDF "PrevSheet(cell ref)" does just that. I have tried running PrevSheet() from within VBA, which I thought was legal, but I get a compile error “Sub or Variable not defined”.

I did not know this was not representative of your actual file or I would not have invested the time in a redesign.

I don't recall this particular problem and a UDF should definitely be able to do this. What exactly are you doing to call PrevSheet that is giving you an error?

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

If you are creating the Jun sheet with a manual copy, I would have to see the result to determine why it creates a circular reference. Do you see what cells are involved in the circular reference?

Looks to me like the circular reference is because cell B4 is calling function PrevSheet(B4). This is the definition of a circular reference. Excel doesn't know your UDF is using the address, not the value, of B4.

Also PrevSheet is not in this file so I am getting a NAME error.

I didn't need that to diagnose the circular reference. In your case the circular reference is benign. You can ignore it, but you can't get rid of it unless you redesign how PrevSheet works. One option would be:

Please Login or Register  to view this content.

This function will always return the data in the corresponding cell from which it is called. That seems to be how you intend to use it anyway. Now you don't need to include the cell as an argument.

Thanks for sharing.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Just followed your link - what a coincidence that that 'someone' has coined the exact same user name as you!!!

Was this the link you meant to share???

https://answers.microsoft.com/en-us/...e-9f224784e7a1

Sorry - I am not following you at all. You said this:

Quite by accident I have just found someone also having problems with the PrevSheet UDF, not only that they had a solution that works! It is at

HTML Code: 

(not sure if I got that right). Maybe my computer took pity on me and went looking on its own! ;-)

But the link goes to one of YOUR threads on another forum.

So where is the thread by the 'someone' who had the working solution?

OK, look, here's what I'd suggest: post the code for the UDF here (with a nod to Gord Dibben, who offered it to you). VBA experts here may be able to help you to tweak it to work as you wish it to. No need to start a new thread - just add it here.

Originally Posted by Tegglet

That returns a #VALUE error!

Sorry, the order of statements needs to change:

Please Login or Register  to view this content.

The lines "Dim rCell As Range" and "Set rCell = Application.Caller" are shown in red in your example; is that significant or just to bring my attention to the differences?

Only to show what I changed.

Regarding the argument not being required, how does it know which cell on the previous sheet contains the required value? For instance when used to get the previous months balance, the UDF entered in cell M4 needs the value in cell N50 on the previous sheet and the UDF in cell N4 needs the value in cell M50 on the previous sheet.

I was just trying to resolve the circular reference issue and focused on B4. I did not notice what was going on in those other cells. This version of the function assumes that if you call it from B4, you want B4 on the other sheet, and so on. If you call it from M4 but need to reference N50, then this will not work for you. Another option is to have it return the sheet name, then use an INDIRECT formula to reference the desired cell.

To wit:

Please Login or Register  to view this content.

And to use:

Formula:

Please Login or Register  to view this content.

Avoids circular reference, and you can insert any cell reference desired in place of B4.