Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Locate day between 2 dates to populate bills due

0

UPDATED:  Sorry, this didn't make any sense.   I have attached my document and have a tab call instructions that explain my document.  It's a workbook that I print out and bind as a booklet.  Tab - Instructions has all of the information for what I'm trying to do and Tabs 1 and 2 have comments on them.

I have a sheet listing all of my bills and the day in which they are due.  I received 26 pay checks I have a sheet like the one below.  I would like automate each sheet (1 for each pay period) to list out all bills due within the pay period.  I don't know if it is a formula or if it would be script. I have yet to find anyone that can figure it out for it to work with a formula of locating the day between the two dates since it crosses months and at the end of the year, crosses the year.

Also, there are several that are due each payday regardless of the day.  For example tithes.

I hope someone can assist, I'm about to pull my hair out and spend too much time every year setting this up, but I love it!

excel
MS Office Version: Office 365 Excel 2312
posted by: windpooh2 (rep: 2) on Wed Jan 24, 2024 at 10:50 pm
Answer
Discuss

Discussion

Hi Windpooh2 and welcome to the Forum.  

Sorry but I'm quite not clear what you're trying to do here. Looking at your file (which had to be repaired for some reason) didn't help too much either.  

I'm guessing you have a sheet showing all bills over the year (or several years) but does that give the dates, the frequency (e.g. every 4 weeks commencing...), specific dates each month or a mix of those per bill? What about the values on that sheet? (Some are fixed but others change or are missing I imagine)

Please EDIT your original question to add this detail (preferably with an example of such an "all bills" sheet included in your file- that will save us time).   Your Answer may depend on which version yso kindly update your Profile on the Forum which currently states:
MS Office Version: nothing entered
so we don't know if newer functions like SORT, FILTER or even INDEX/ MATCH can be used by you.  

A fuller question and the version information will help anyone trying to help you (but please note I doubt I'll have time before next week since I'll be busy for the next 3 days, sorry).
John_Ru (rep: 6142) Jan 25, '24 at 9:28 am
@windpooh2

When I read your question my first thought was that possibly Datedif() would be the way to go. But when I looked at your file I arrived at the same conclussion as John did. Maybe if more details are provided as John has asked for, I might be able to provide a solution.
WillieD24 (rep: 557) Jan 25, '24 at 10:15 am
I will update and clarify  Thank you so much for responding.  And so glad to be here.  Looks like I can learn A LOT!
windpooh2 (rep: 2) Jan 25, '24 at 5:44 pm
@windpooh2

Thank you for adding the file. After giving it a quick look I have a couple of questions. 1) On sheet "1", row 2 is unique form the rest of the sheets. It has a date in "A2". On sheet "2" you indicate that the date is determined by sheet "1" cell "A2" but in actuallity it is cell "G3". Therefore, this row can the row be deleted? When building a file of this type it is best if all sheets have the same layout. 2) How is the text in col "D" & "E" detemined and entered? Manually or some other way?
These answers will help in working on a solution.
WillieD24 (rep: 557) Jan 25, '24 at 11:27 pm
@windpooh2

I have the basic concept figured out, I just need to do some trial and error to get the syntax correct. Working with dates can be troublesome and complex.
WillieD24 (rep: 557) Jan 26, '24 at 3:38 pm
Thank you so much WilieD24.  Let me see if I can answer your questions.  
1. Oh, You are right, I forgot I corrected that.  Yes, that row can be deleted.
2. Columns "D" & "E" are actually checkboxes, but it must not have downloaded or uploaded properly.  That shouldn't say FALSE.  When it is in paper form it's a checkbox and I physically check the box for paid or cleared.  
windpooh2 (rep: 2) Jan 26, '24 at 5:42 pm
@windpooh2

1. I chanced it and have already deleted the row so all payperiod sheets are the same.
2. Col "D" & "E" - I have added a "BeforeDoubleClick" worksheet event - double-click a cell and it will enter or remove a checkmark.
I'm only having 30 - 45 minute blocks at a time to work on this; part of that time is spent on refocusing. I am confident I will have a solution for you to review but it will probably be a couple of days before I finalize it.
WillieD24 (rep: 557) Jan 27, '24 at 12:21 am
WillieD24.  Thank you so much. Please don't feel you need to rush. I totally appreciate any time that you have to spend on it.  
windpooh2 (rep: 2) Jan 27, '24 at 2:00 pm
@windpooh2

When bills are added to each pay period how do you prefer them to be orderd: by Bill/Charity # or by due date?
WillieD24 (rep: 557) Jan 27, '24 at 11:03 pm
Hi, I'm sorry, I was having trouble accessing on my mobile while I was out.  I like them by due date.  I like the ones for every paycheck to be up at the top.  (Those are like tithes, mortgage groceries, etc)

Right now, I list them in the order they are due within that pay period.  28th 29th, 31st 1st 2nd, etc.  However, It doesn't really matter to me.  I can do bill number and list them in the order of the due date on the bill listing page.  

Whatever is easiest for you I can work with.
windpooh2 (rep: 2) Jan 29, '24 at 5:24 pm
This is absolutely exactly what I wanted/needed. Thank you so so so much.  I did receive an error 450 when I tried to use the reset sheets macro.  You are my life saver.

For the shape, using 365, whenever I tried to create shape, it takes me to the draw and I can't use a shape, I have to draw the shape and for any text I have to add a text box. It's a pain.  But I'm using the desktop version now.  

Thanks for the suggestions with the colors, will definitely make some changes.

The "B" hidden sheets are displayed on the left side of the booklet when printed.  They are for entering savings goals.  

I guess when downloading some of the formatting may have been changed or I did a terrible job. It was orginially in Google years ago. My apologies!
windpooh2 (rep: 2) Jan 29, '24 at 9:12 pm
Add to Discussion

Answers

0
Selected Answer

Hello windpooh2,

It took a lot of trial and error, and as I mentioned earlier, working with dates can be troublesome and complex. There was also some time wasted when I forgot to save my work.  :-(

I finally finished it and the file (Revision 1) is attached. On Sheet("1") I have added a bunch of shapes with comments explaining what I have done. I was surprised at the formatting that needed correcting.

In this final version (ExceI 2016) I wrote 3 macros: 1 to get the regular bills; 1 to get the special event (Sheet 30) bills; and one to reset/clear the sheets (use this after creating a copy to use for the following year; or make a copy of this version without any data to use as a master copy).

All of mine and your comments on Sheets 1 & 2 can be deleted once you have reviewed this finished version and are happy with it.

Update 11:48 PM

There was a minor error which I have fixed. (this fix will also make editing easier)

The repaired file is attached under the same name so delete the previous file you downloaded.

If there is something more that you need , just ask.

If this solution meets your needs, please mark my answer as Selected

Cheers   :-)

posted by: WillieD24 (rep: 557) on Mon Jan 29, 2024 at 7:31 pm
Discuss

Discussion

Wasn't sure where to put this.   

You are my HERO! This is absolutely exactly what I wanted/needed. Thank you so so so much.  I did receive an error 450 when I tried to use the reset sheets macro.  You are my life saver.

For the shape, using 365, whenever I tried to create shape, it takes me to the draw and I can't use a shape, I have to draw the shape and for any text I have to add a text box. It's a pain.  But I'm using the desktop version now.  

Thanks for the suggestions with the colors, will definitely make some changes.

The "B" hidden sheets are displayed on the left side of the booklet when printed.  They are for entering savings goals.  

I guess when downloading some of the formatting may have been changed or I did a terrible job. It was orginially in Google years ago. My apologies!
windpooh2 (rep: 2) Jan 29, '24 at 9:28 pm
@windpooh2

I'm glad I was able to help. The code error has been fixed. ( see above) The method I first used is no longer accepted.

Thanks for selecting my answer.

Cheers   :-)
WillieD24 (rep: 557) Jan 29, '24 at 11:52 pm
@Willie

Looks like Windpooh2 was pleased with your work- well done! I found some time to look at your Answer file but didn't get far! Clicked the "Reset Sheets" button on sheet and Excel went into an infinite loop. Why? Well, look at the bold section in the code extract below (from Bi-Weekly Budget - Revision- 1.xlsm):
Sub Rest_Sheets()
 
...
'user clicks YES button
If answer = vbYes Then
 
Application.ScreenUpdating = False   ' allows macro to run faster
Do Until PPnbr > 26
Range("D2").ClearContents
Range("A14:C35").ClearContents
Range("F6:G35").ClearContents
Range("A36:B49").ClearContents
Range("C37:G49").ClearContents
 
Loop
...


... see next point (too long for a single comment)
John_Ru (rep: 6142) Feb 3, '24 at 6:59 am
... continued:

PPbnr never gets incremented and it loops, continually resetting only sheet 1 (the active sheet, from the button click) .
I think your code should read something like:
...
'user clicks YES button
If answer = vbYes Then
 
    Application.ScreenUpdating = False   ' allows macro to run faster
    Do Until PPnbr > 26
        ' act on indexed sheet
        With Sheets(PPnbr)
            .Range("D2").ClearContents
            .Range("A14:C35").ClearContents
            .Range("F6:G35").ClearContents
            .Range("A36:B49").ClearContents
            .Range("C37:G49").ClearContents
        End With
        ' increment sheet index
        PPnbr = PPnbr + 1
    Loop
 
End If
Application.ScreenUpdating = True   ' reset
End Sub

If so, the code resets all sheets quickly.

When I click "Get Bills", many sheets get updated but in each case row 2 seems to show January in C2, D2 is blank (and so G2 is an incorrect date like 1/13/1900). I guess I've missed something (I didn't have time to chech any instructions) but please check if necessary.
John_Ru (rep: 6142) Feb 3, '24 at 7:00 am
@John

You must have downloaded the initail version I posted. I too saw those problems and fixed them. I had to move reseting Sheet(1) "D2" out of the loop because that caused the date issues. Also, after the sheets are reset, the starting date must be manually entered into Sheet(1) "D2".  I also added the line to increment PPnbr. The file currently attached has the revisions.

Cheers   :-)
WillieD24 (rep: 557) Feb 3, '24 at 7:31 pm
@Willie- thanks for the clarification and ensuring a working file is available . It's odd that I got an incorrect file since I downloaded it only yesterday and the filename was ...Revision 1.xlsm (as now and as I said above). 
John_Ru (rep: 6142) Feb 4, '24 at 2:32 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login