Using VBA to break out a whole month even if zero data

[RomoBot]

Hello, this to me feels a bit complicated but I'm hoping to find an elegant solution. I am new to posting here so please let me know what all additional information I need to provide if I am missing something. Also as a caveat, I am very rudimentary at VBA - I basically know how to copy and paste from what people post as solutions.

I need to track number of posts per day, even if they are zero, based on a downloaded CSV file in a specific format (only including days there were posts) and also combine dates that are the same.

Basically: I need to turn this:

Into this:

Using a macro that can also recognize the length of the month (not always July.) It also has to all be done as a macro, because I am turning it over to the client who wants to just paste the raw data and have (the second picture) appear.

Any help would be much appreciated!

 

[maabadi]

For first part , after open CSV file with excel, add this macro & run it (if format is same as image uploaded):

Sub Macro3()
    Dim Lr As Long, i As Long, C As Long, M As Long
    Range("E1:G1").Value = Range("A1:C1").Value
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("E2").Value = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Min(Range("A2:A" & Lr)), -1) + 1
    C = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Max(Range("A2:A" & Lr)), 0) - Range("E2").Value + 1
    Range("E2").EntireColumn.NumberFormat = "dd-mmm"
    Range("E2").AutoFill Destination:=Range("E2").Resize(C), Type:=xlFillDefault
    Range("F2").Formula = "=IF(SUMIFS(B$2:B$12,$A$2:$A$12,$E2)=0,"""",SUMIFS(B$2:B$12,$A$2:$A$12,$E2))"
    Range("F2").AutoFill Destination:=Range("F2:G2"), Type:=xlFillDefault
    Range("F2:G2").AutoFill Destination:=Range("F2:G" & C + 1), Type:=xlFillDefault
    Range("F2:G" & C + 1).Value = Range("F2:G" & C + 1).Value
End Sub

For Comments & Likes, How we can recognize them from CSV file?

 

[maabadi]

if your Data same as Last image with 5 rows Try this:

Sub Macro3()
    Dim Lr As Long, i As Long, C As Long, M As Long
    Range("G1:I1").Value = Array("Date", "# of Posts", "Engagements")
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("G2").Value = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Min(Range("A2:A" & Lr)), -1) + 1
    C = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Max(Range("A2:A" & Lr)), 0) - Range("G2").Value + 1
    Range("G2").EntireColumn.NumberFormat = "dd-mmm"
    Range("G2").AutoFill Destination:=Range("G2").Resize(C), Type:=xlFillDefault
    
    Range("H2").Formula = "=IF(SUMIFS(B$2:B$12,$A$2:$A$12,$G2)=0,"""",SUMIFS(B$2:B$12,$A$2:$A$12,$G2))"
    Range("I2").Formula = "=IF(SUMIFS(E$2:E$12,$A$2:$A$12,$G2)=0,"""",SUMIFS(E$2:E$12,$A$2:$A$12,$G2))"
    Range("H2:I2").AutoFill Destination:=Range("H2:I" & C + 1), Type:=xlFillDefault
    Range("H2:I" & C + 1).Value = Range("H2:I" & C + 1).Value
End Sub

 

[RomoBot]

if your Data same as Last image with 5 rows Try this:

Sub Macro3()
    Dim Lr As Long, i As Long, C As Long, M As Long
    Range("G1:I1").Value = Array("Date", "# of Posts", "Engagements")
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("G2").Value = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Min(Range("A2:A" & Lr)), -1) + 1
    C = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Max(Range("A2:A" & Lr)), 0) - Range("G2").Value + 1
    Range("G2").EntireColumn.NumberFormat = "dd-mmm"
    Range("G2").AutoFill Destination:=Range("G2").Resize(C), Type:=xlFillDefault
   
    Range("H2").Formula = "=IF(SUMIFS(B$2:B$12,$A$2:$A$12,$G2)=0,"""",SUMIFS(B$2:B$12,$A$2:$A$12,$G2))"
    Range("I2").Formula = "=IF(SUMIFS(E$2:E$12,$A$2:$A$12,$G2)=0,"""",SUMIFS(E$2:E$12,$A$2:$A$12,$G2))"
    Range("H2:I2").AutoFill Destination:=Range("H2:I" & C + 1), Type:=xlFillDefault
    Range("H2:I" & C + 1).Value = Range("H2:I" & C + 1).Value
End Sub

The last (attached) image is not supposed to be there, I didn't realize it had attached and being new to this forum, can't figure out how to edit my original post to remove it. The first picture in the post is how I have the data (the delineation of comments and likes isn't needed)

 

[RomoBot]

For first part , after open CSV file with excel, add this macro & run it (if format is same as image uploaded):

Sub Macro3()
    Dim Lr As Long, i As Long, C As Long, M As Long
    Range("E1:G1").Value = Range("A1:C1").Value
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("E2").Value = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Min(Range("A2:A" & Lr)), -1) + 1
    C = Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Max(Range("A2:A" & Lr)), 0) - Range("E2").Value + 1
    Range("E2").EntireColumn.NumberFormat = "dd-mmm"
    Range("E2").AutoFill Destination:=Range("E2").Resize(C), Type:=xlFillDefault
    Range("F2").Formula = "=IF(SUMIFS(B$2:B$12,$A$2:$A$12,$E2)=0,"""",SUMIFS(B$2:B$12,$A$2:$A$12,$E2))"
    Range("F2").AutoFill Destination:=Range("F2:G2"), Type:=xlFillDefault
    Range("F2:G2").AutoFill Destination:=Range("F2:G" & C + 1), Type:=xlFillDefault
    Range("F2:G" & C + 1).Value = Range("F2:G" & C + 1).Value
End Sub

For Comments & Likes, How we can recognize them from CSV file?

Just tested this and it works EXACTLY how I need it to! THANK YOU!

 

[maabadi]

You're Welcome & Thanks for Follow-up.

 

[RomoBot]

You're Welcome & Thanks for Follow-up.

A follow up - now they'd like to be able to do the same but with multiple months of data. Is there a modification to the original that would recognize the first date in a string and the last date and include from the beginning of the month of the earliest date to the end of the month of the latest date?

 

[maabadi]

Do you Test macro with Multiple month Data? I think it works with that without problem.

 

[RomoBot]

Do you Test macro with Multiple month Data? I think it works with that without problem.

I did attempt to -- it made all the months but did not pull over the engagements and # of posts data except for the first 30-day period

 

[RomoBot]

I realized the issue! My new data set was a lot longer (42 rows) -- so I amended this line:

Range("F2").Formula = "=IF(SUMIFS(B$2:B$12,$A$2:$A$12,$E2)=0,"""",SUMIFS(B$2:B$12,$A$2:$A$12,$E2))"
to
Range("F2").Formula = "=IF(SUMIFS(B$2:B$3000,$A$2:$A$3000,$E2)=0,"""",SUMIFS(B$2:B$3000,$A$2:$A$3000,$E2))"

To be sure it incorporates any possible length of data set. Thanks again!