budget calendar, help handeling February date overlaps

[mperrah]

13878
Hello vbaExpress team,
I have been putting together a budget calendar using tools I've found here.
This uses index-match, vlookup, datavalidation lists, named ranges, date functions.
It will color the date cell for paydays green(1st and 15th in my case)
Colors today's date yellow.

I have a sheet named "Bill" with the bill name and amount in column C and D.
I organize them by date due in the month.
on the sheet named "Cal" we select from the drop down in "J1" the year and month/year combo in "C1"
The calendar then looks up the bill info for that date range and populates the days and billing info,
including weekly sum totals and a monthly total.

My issue is:
If I have a bill due on the 29th to 31st and display February, that bill info is left out and only the next months info is displayed.
I use some vba for the cell color, but mostly formula for the data.
Not sure how to get overlapping data to display correctly.
I guess if I could have a second line of data in the date cell for any overlap would be great.
If I need to move the code from formula to vba would be great too.
I started with formula and got most of it working so hadn't needed vba yet, maybe now I do...

[SamT]

How do you get a bill that is due on Feb 31?

[mperrah]

Hello SamT,

The way I set up the table for the bill list was to add an item at a time based on the day number.
I didnt have a way of adding multiple entries for one day due to using vlookup.
So I just made 1 to 31 and populated the days as close to the actual due date as I could (moving ahead if overlapped so I'm not late)
I ran into issues if the data goes over the available days in the current month...
I suppose I could use vba with a loop and find day matches in my list then match them to the target date cell with a vbcr between each if multiples are found.

[mperrah]

13897
I modified this macro I found on Microsofts' site.
I still am looking for a fix for a short Month.
I made a generic page to align bills by date, 1-31.
but obviously not ever month has 31 days
I'm thinking of an inputbox that pops up if they want a February Calendar and there is a value below the 28th,
asking them what day they want to move the bill forward..

Also, not sure how to set up the sheet for multiple bills due on same day.
I think the bill layout would need to be very different, or the calendar would need mutliple rows for each day,
but not sure how to manage and populate that way...
If there was a way to populate the calendar day cells with multiple lines in each cell with matching date values..
I could just have a random order list and fill the day cells based on the due date?

[SamT]

The person in this post: Calender control (http://www.vbaexpress.com/forum/showthread.php?53091-Calender-control), is working with a calendar that has 3 cells in each day.

[mperrah]

I copied this with macro recorder
It errors out when I try to run it.

I'm trying to add the values across the row. They have the name with the amount combined,
so I find the numbers to the right of the "-" mark. Then plan to sum them for each week.


Sub Macro12()

Sheets("Test2").Range("H4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IFERROR(TRIM(RIGHT(RC1,LEN(RC1)-(FIND(""-"",RC1)))),0),IFERROR(TRIM(RIGHT(RC2,LEN(RC2)-(FIND(""-"",RC2)))),0),IFERROR(TRIM(RIGHT(RC3,LEN(RC3)-(FIND(""-"",RC3)))),0),IFERROR(TRIM(RIGHT(RC4,LEN(RC4)-(FIND(""-"",RC4)))),0),IFERROR(TRIM(RIGHT(RC5,LEN(RC5)-(FIND(""-"",RC5)))),0),IFERROR(TRIM(RIGHT(RC6,LEN(RC6)-(FIND(""-"",RC6)))),0),IFERROR(TRIM(RIGHT(RC7,LEN(RC7)-(""-"",RC7)))),0))"
Range("H5").Select
End Sub

[mperrah]

13926
Here is the file I have so far.
The macro in question is in module Mod2 near the end.

I run sub Main() to make the calendar based on the month year in cell B1 (sourced from the value in the yellow cells)
then I am trying to add the values for each Week.

There are 5 rows for each day. I have a Sum formula in the uppermost cell row for each week (in line with day numbers)

[mperrah]

ugly, but works

Sub sumWeekly()
Dim r, x As Long
Dim wsNC As Worksheet

Set wsNC = Sheets(Sheets.Count)

With wsNC
For r = 3 To 33 Step 6
.Cells(r, 8).FormulaR1C1 = "=Sum(R[1]C:R[5]C)"
For x = 1 To 5
.Cells(r + x, 8).FormulaR1C1 = "=SUM(IFERROR(TRIM(RIGHT(RC1,LEN(RC1)-(FIND(""-"",RC1)))),0),IFERROR(TRIM(RIGHT(RC2,LEN(RC2)-(FIND(""-"",RC2)))),0),IFERROR(TRIM(RIGHT(RC3,LEN(RC3)-(FIND(""-"",RC3)))),0),IFERROR(TRIM(RIGHT(RC4,LEN(RC4)-(FIND(""-"",RC4)))),0),IFERROR(TRIM(RIGHT(RC5,LEN(RC5)-(FIND(""-"",RC5)))),0),IFERROR(TRIM(RIGHT(RC6,LEN(RC6)-(FIND(""-"",RC6)))),0),IFERROR(TRIM(RIGHT(RC7,LEN(RC7)-(FIND(""-"",RC7)))),0))"
Next x
Next r
End With
End Sub

[mperrah]

13932
I got this working close to what I was planning.

You just add your bill/events in the green area with name, amount and due day.
Update the Year in H1 and pick the Month from G1.
Then click the "Make Calendar" button.
A new sheet will be created titled as the Month-Year.
All your bills/events will be filled in on the corresponding days.
And the 1st and 15th will be highlighted Green (my paydays).
And a subtotal for each weeks bills will be summed at the right of the week.

If you have a bill/event after the 28 for February, a msgbox will inform you and select the first issue occurrence.
It would be cool to have an input box to pick a optional day to move the events to, but this is functional for now.

hope this helps people out there,
I learned a lot making this.

[mperrah]

I was able to make an input box come up if a date of a bill is not included in the chosen month,
prompting user for a new day to be entered the bill.
I'm sure some debugging is necessary to catch bad input, but this works pretty good.
It does change the value on the Bills page to the updated date as well, so future issues will be avoided.


Sub insertBills()
Dim b, r, c, x, lrV As Long
Dim wsNC, wsB As Worksheet
Dim aBill As Variant
Dim dCnt, nDate, dTarget

Set wsNC = Sheets(Sheets.Count)
Set wsB = Sheets("Bills")

lrV = wsB.Cells(Rows.Count, 2).End(xlUp).Row

ReDim aBill(1 To lrV, 1 To 5)

' test for a bill due on a day thats not in the month selected
For t = 2 To lrV
If wsB.Cells(1, 7).Value = 2 And _
wsB.Cells(t, 4).Value > 28 Then
nDate = InputBox("Please enter new day for bill '" & wsB.Cells(t, 2).Value & "' not included in selected month", "Billing Date Conflict")
wsB.Cells(t, 4).Value = nDate
End If
Next t

aBill = wsB.Range("A2:E" & lrV).Value

With wsNC
dCnt = 0
For b = LBound(aBill, 1) To UBound(aBill, 1)
For r = 3 To 38 Step 6
For c = 1 To 7
dCnt = dCnt + 1
If .Cells(r, c).Value = aBill(b, 4) Then
For x = 1 To 5
If .Cells(r + x, c).Value = "" Then
.Cells(r + x, c).Value = aBill(b, 5)
Exit For
End If
Next x

End If
Next c
Next r

Next b

End With
End Sub

[mperrah]

13999
added some sheet functionality.
When adding a bill to the list it is line numbered and colored automatically.
and the formula to concatenate is added too for column E
A pop up appears when making a calendar with a bill date that is not included in the chosen month.
The user is asked to pick a new date for the offending bill.
the list is updated with the new date so future conflicts will be avoided.
Added a clear list button so starting from scratch is easier.
cell comments and text box on sheet for instructions too.
Reformatted the week summary so you only see the subtotal.
You can add to the calendar manually and the weeks subtotals will still calculate.
Thinking of making a Boolean at calendar creation to ask if user wants subtotals or not...