+ Reply to Thread
Results 1 to 12 of 12

I want to auto populate my bills each month into a calendar I built for budgeting

  1. #1
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    I want to auto populate my bills each month into a calendar I built for budgeting

    I'm still new to VBA, Took a long break and started studying C++, so now I'm all confused as to how to get this done in VBA. So here is an image of the file. I have my bills located on the same sheet Starting at B76:D100, B76,C76, and D76 are titles Date, Item, Amount The Date is a formula. I want to input this info into the calendar with a button. As you can see A13 is also a formula and D13 is a different formula not sure if that makes a difference or not. I want to input my bills into rows 16 to rows 22 where the dates match. Some will have multiple entries where there is multiple bills on the same day. Any help is greatly appreciated. Thanks in Advance!
    Attached Files Attached Files
    Last edited by scottyb1977; 01-21-2022 at 12:59 PM. Reason: added a sample sheet instead of img file.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Rather than a picture of an Excel sheet, which we can't edit (or even read very well), it would be better if you attached a sample Excel workbook, by following the directions given in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Sorry about that it's been awhile since I've posted anything and wasn't sure about the protocol. Hope this helps and hope you can help me. Thanks again!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    I'm not sure why you have a formula to generate the dates in B77 downwards, as presumably you have to type in the day for each entry. Is it too much trouble to enter the date directly? Also, if you change the month in V9 then all the dates will change to that month.

    I think it would be better to have the list of bills on one sheet, where you enter the date, category and amount (along with the payment type, P or X, as in the Pay column of the calendar, although I'm not sure how you use this), and then a separate sheet for the calendar display. The bills can then be displayed automatically on the calendar using formulae, in conjunction with a helper column on the data entry sheet.

    Is this something you could work with?

    Pete

  5. #5
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    I can work with whatever you think can make it work right. The X,P drop down list is more of a deposit and cross off thing. The P stands for paid or deposit and the X is for when it clears the bank it highlights the items in the row.

  6. #6
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    To be fair I don't think I asked a question. I have tried to write several code attempts and I have failed.
    This is one of the codes I attempted but it only turned the cell to true, Plus it was the cell that had the
    date and I don't want to change the date values at all. I want to input the data from sheet 2 into a cell two rows down
    then go to the next item on sheet 2 and so on until all dates have been put into the correct cells. Again any help is greatly appreciated.
    So my question is How do I match dates and not change the date value to true? and how do I copy the data into right cells on the calendar?

    Please Login or Register  to view this content.
    Last edited by scottyb1977; 01-25-2022 at 10:22 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    I did a bit more work on it last night, but I've been busy on other things today (weekend) so it is not finished yet. I'll post it when I have it all set up.

    I can get the multiple bills to display on the calendar using formulae, but it seems to mess up some of the formulae that you already had in there, so they produce errors. It might take me a while to work out what you intended with your formulae.

    Pete

  8. #8
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Great I appreciate the time you are taking. It's no rush. I just didn't see any replies and I realize I never really asked a question. Thanks again.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    I think this is working now as it should. You will notice that I have moved the data to its own sheet called bills, where you will enter data into columns B to D as appropriate. I have fixed the values of the dates that you had before.

    I also have a formula down column A, which sets up a unique reference in a format that can be used by the calendar sheet - I've copied this down to row 100, but you can copy it further if you have more data. This is the formula in A2:

    =IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

    basically, the date plus a count of each particular date.

    In the Calendar sheet I have used this formula in B15:

    =IFERROR(VLOOKUP(A$13&"_1",Bills!$A:$D,3,0),"")

    and this one in C15:

    =IFERROR(VLOOKUP(A$13&"_1",Bills!$A:$D,4,0),"")

    The only difference is the number in red. These 2 formulae can be copied down to row 22, but the value shown in purple above will then need to be changed to _2, _3, _4 and so on on successive rows.

    The block of cells can be copied across into the corresponding columns for the other days of the week. In copying down for the other weeks, the reference to the row shown in blue (i.e. 13) would need to be changed.

    I also had to change the formulae that you had on the first row of each week to avoid #VALUE errors (by using SUM ).

    I've added a bit of test data for March, so you can see that you can enter data in any order, and by changing the month in V9 you can see the display change automatically.

    I have not looked at any of your macros, but they may need to change a bit due to the changed layout.

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Really appreciate your help. I will look at what you have done, but I think I got it figured out too.
    All I had to do was unmerge my cells from A13:Y13,A23:Y23,A33:Y33, etc. Although, I feel that the code I wrote could be done more efficiently.
    However it gets the job done.Again I do appreciate your time, Thank You so much for your work. Below is the code I wrote.

    Please Login or Register  to view this content.
    Last edited by scottyb1977; 01-25-2022 at 10:23 AM. Reason: added code tags

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Glad you figured it out in the end.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  12. #12
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: I want to auto populate my bills each month into a calendar I built for budgeting

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Monthly calendar of bills - pull out bills for a month
    By Keelin in forum Excel General
    Replies: 8
    Last Post: 04-10-2016, 04:57 PM
  2. Replies: 3
    Last Post: 09-21-2013, 10:10 AM
  3. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  4. [SOLVED] Auto populate cells from data in a 6 month range starting with the current month
    By ecarnley349 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:32 PM
  5. Replies: 2
    Last Post: 06-14-2012, 01:55 PM
  6. Calendar VBA auto filling week and month based on calendar entry.
    By perrymagic in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2011, 02:00 PM
  7. Replies: 0
    Last Post: 04-25-2011, 04:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1