Is this even possible? VBA question about adding rows for blank data...

[nkemp15]

I have a report that I run that will pull data for how many calls a team received for every 15 minute interval of the day. The issue I'm having with this report, is if there were no calls received, no data is put on the report. For example, it will give information for 1:00 1:15 1:30 2:00 & 2:15. In my example, no calls were received between 1:45 and 2:00, so 1:45 is completely missing from the report.

Is it possible to write a VBA script that will look through the times, and add in the missing rows with the blank times? I don't need any other data (since it would all be zeros anyways) I just need the rows to be added so management can visualize the times with zero calls.

 

[VorLag]

Yes, I would think that it's not very hard to do that, but I don't think you necessarily need to have a Macro add in times if you want it all sorted into a new tab or workbook anyway. You might just want to create a template that includes all of those times you want to look at and build the Macro on top of that.

 

[Momentman]

Do you mean something like this(data structure) or how is your data structured

Excel 2010
	A	B	C	D	E
2	Time	Calls		Time	Calls
3	01:00	5		01:00	5
4	01:15	6		01:15	6
5	01:30	7		01:30	7
6	02:00	4		01:45	0
7				02:00	4
Sheet1

That's like the before and after, is that right?

 

[nkemp15]

Exactly. How would I do that would be my next question.

 

[Momentman]

This is how i think, PLEASE TRY ON A COPY OF The WORKSHEET

Sub addtimes()
    Dim i As Integer, j As Integer
    For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
        a = 1 * Format(96 * (Range("A" & i) - Range("A" & i - 1)), "#")
        If a <> 1 Then
            For j = 1 To a - 1
                Rows(i).Resize(1).Insert
                Range("A" & i).Value = Range("A" & i + 1).Value - 1 / 96
                Range("B" & i).Value = 0
            Next j
        End If
    Next i
    
End Sub

The "96" is because 15mins is 1/96th of a day(15/60/24). If we were using 30mins, everywhere you have 96 would be 48 and son on

 

[nkemp15]

That doesn't work at all. I get an error that says "Type Mismatch"

I don't know if this matters but there are dates in there as well.

 

[Momentman]

Dates in where? What column is your data in? Please post a sample of your spreadsheet.

I wrote the cide based on perceived idea of your data and it works flawlessly for me

 

[nkemp15]

It's per 30 minutes and not per half hour also. I am new to this report and gave wrong information. I apologize.

 

[Momentman]

Looking at row 31 and 32, one is 6:30Pm the previous dAy while the other is 9AM the next day, how does the program know whether or not to add new rows between these times 'cos i presume 6:30Pm was the last call for that day....or do you only insert new rows when the day hasn't changed?

 

[nkemp15]

We usually run the report on a monthly basis. We would need to know the times from 6:00 till 24:00 every day.