Change 15 Minutes into 30 Minutes Intervals VBA

[kamiljaku]

Im struggeling with a macro which needs to convert 15 min intervals to 30 min

Original data looks like

00:00	2	3	5
00:15	2	1	4
00:30	5	2	6
00:45	5	3	4
01:00	7	4	2
01:15	7	2	3

<tbody>
</tbody>

and I would need such an outcome somewhere right or left from the original

00:00	4	4	9
00:30	10	5	10
01:00	14	6	5

<tbody>
</tbody>

This is how far I have come

Sub IEX2()


Dim m As Integer
Dim i As Integer




For m = 1 To 10
Cells(m, 2).Value = WorksheetFunction.Sum(Cells(m * 2 - 1, 1).Resize(2))
Next


End Sub


It works even but I need to loop it somehow so it converts all coming columns and not just one. Could someone help?

 

[Fluff]

How about

Sub IEX2()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, i As Long
   
   Ary = Range("A1").CurrentRegion.value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary) Step 2
      i = i + 1
      Nary(i, 1) = Ary(r, 1)
      For c = 2 To UBound(Ary, 2)
         Nary(i, c) = Ary(r, c) + Ary(r + 1, c)
      Next c
   Next r
   Range("A1").CurrentRegion.ClearContents
   Range("A1").Resize(i, UBound(Ary, 2)).Value = Nary
End Sub

 

[Norie]

Couldn't you use a simple SUMIFS formula?

For example with 00:00, 00:30 etc. in column H and the original data in A1:D6.

=SUMIFS(B$1:B$6,$A$1:$A$6,">="&$H1,$A$1:$A$6, "<"&$H1+TIME(0,30,0))

 

[kamiljaku]

How about

Sub IEX2()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, i As Long
   
   Ary = Range("A1").CurrentRegion.value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary) Step 2
      i = i + 1
      Nary(i, 1) = Ary(r, 1)
      For c = 2 To UBound(Ary, 2)
         Nary(i, c) = Ary(r, c) + Ary(r + 1, c)
      Next c
   Next r
   Range("A1").CurrentRegion.ClearContents
   Range("A1").Resize(i, UBound(Ary, 2)).Value = Nary
End Sub

Works like a dream. Now I just need to figure out how this is actually working as Im a beginner

 

[Fluff]

Glad we could help & thanks for the feedback

 

[kamiljaku]

Couldn't you use a simple SUMIFS formula?

For example with 00:00, 00:30 etc. in column H and the original data in A1:D6.

=SUMIFS(B$1:B$6,$A$1:$A$6,">="&$H1,$A$1:$A$6, "<"&$H1+TIME(0,30,0))

I could and I have worked out my own formula to do this

index(B$5:B$100,rows(B$5:B8)*2)+index(B$4:B$99,rows(B$4:B7)*2)

but I've needed it in VBA because it is just a part of a bigger macro. Although thanks for your tip, I will go trough your formula as it seems pretty simple.