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

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]00:00[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]00:15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]00:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]00:45[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01:00[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01:15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]00:00[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]00:30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01:00[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]



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.