Every Month totals

[Shailender]

Hello,
I have the data, where I need the totals on the every month end date through VBA LOOP. If the month end falls on Saturday and Sunday the total should be there above that 2 days and then again it should start from next row. I have attached the excel sheet for your reference and i have colored it with red color.

Please help .

Thank you!

 

[Shailender]

Can anyone help me out on this..?

 

[Marc L]

Hi !

If the month end falls on Saturday and Sunday the total should be there above that 2 days and then again it should start from next row.

So row #44 of your attachment does not respect this ‼
Maybe the reason why you did not get any answer …
So we expect at least a correct attachment.

Real worksheet already has some total row ?!
If not join a worksheet respecting real one.

Did you begin a code ? As it's at beginner level …
​

 

[Shailender]

Marc, Sorry for the confusion, i have attached the sheet with updated figures.

I didn't began the code, please help me out on this. thank you.

 

[Marc L]

Sub Demo()
          Dim Rg As Range, Rf As Range, D As Date, W%
          Application.ScreenUpdating = False
    With Sheet1.Cells(1).CurrentRegion.Columns(1).Cells
          Set Rg = .Item(2)
          D = Rg.Value
        While Rg.Value2 > ""
            D = DateSerial(Year(D), Month(D) + 1, 1)
            Set Rf = .Find(D, Rg)
             If Rf Is Nothing Then
                Set Rf = .Item(.Count)(1 - IsDate(.Item(.Count).Value))
                    Rf.Clear
             Else
                   W = Weekday(D)
                If W < 3 And IsDate(Rf(0).Value) Then Set Rf = Rf(1 - W)
             End If
            If IsDate(Rf(0).Value) Then
                Rf.EntireRow.Insert
                Rf(0, 2).Value = Application.Sum(Range(Rg(1, 2), Rf(-1, 2)))
            End If
          Set Rg = Rf
        Wend
          Set Rg = Nothing:  Set Rf = Nothing
    End With
          Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Shailender]

Marc, thank you for your help, it is working. I am new to VBA, could you please explain line by line what the code is exactly doing..?

 

[Marc L]

From a starting cell, calculate the first day of next month,
find this date, calculate its weekday in order to move up if needed,
insert a row and compute a sum for current month …

If you need help, place text cursor in code on a statement
and hit the F1 key ! A good way to learn …
​

 

[Shailender]

Thank you Marc for your help, Sorry to trouble you, i could not get the "Place text cursor in code on a statement and hit the F1 key". Could you please in detail about it...

 

[Marc L]

It's yet the details : just place text cursor on a statement then hit F1 key …​

 

[XcelThug]

HI There,
This code is lengthy but it will work.
In sheet2 , enter the first date of Feb and autofill it down so as to get all the first days of the months.
this will become the activecell value and the macro will run till the activecell is empty

Attached has the solution

 

[Shailender]

Hello, Xcelthug, the code is working. Thank you for your response.