HELP!! Macro Deletes Last Interval
 

Ok,

I have a macro that adds column A and B for sequential time intervals
(Its confusing see the example)

IE:

Before the Macro is Ran;

column A column B

05:00 1
05:00 1
05:00 0


After the Macro is Ranl

column A column B

05:00 2


Therefore it takes the numbers in column B and adds them to the time
interval and creates a SUM...the problem with the macro is when it
gets to the last set of intervals it deletes it and finshes without
errors, therefore if I was to go all the way through the intervals of
00:00-24:00 I would end up losing the 24:00 interval PLEASE HELP!!! e-
mail me at if you want


Sub AnsweredMath()

Dim TimeInv
Dim cnt As Long
Dim total As Long


'sort the data
Range("A1").Activate
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

'Start at the second row - assumes will always be the first time
interval
Range("A1").Activate
TimeInv = ActiveCell.Value 'get the first range in the sheet;
assumes info starts at A1

While ActiveCell < "" 'while the current cell is not blank -
processes until it hits a blank cell

If ActiveCell.Value < TimeInv Then
ActiveCell.EntireRow.Insert 'insert a row and write out
values
ActiveCell.Value = TimeInv
ActiveCell.Offset(0, 1).Activate
ActiveCell.Offset.Value = total
total = 0 'reset total
cnt = 0 'reset cnt
ActiveCell.Offset(1, -1).Select 'go to the next row
TimeInv = ActiveCell.Value
Else
'capture count in varibles, then delete the row
cnt = ActiveCell.Offset(0, 1).Value
total = cnt + total
ActiveCell.EntireRow.Delete
End If

Wend

End Sub

HELP!! Macro Deletes Last Interval
 

If I recall correctly, the twentyfour hour clock goes from 00:00 to 23:59.
There is no 24:00. 00:00 = Midnight, 12:00 = Noon.

" wrote:

Ok,

I have a macro that adds column A and B for sequential time intervals
(Its confusing see the example)

IE:

Before the Macro is Ran;

column A column B

05:00 1
05:00 1
05:00 0


After the Macro is Ranl

column A column B

05:00 2


Therefore it takes the numbers in column B and adds them to the time
interval and creates a SUM...the problem with the macro is when it
gets to the last set of intervals it deletes it and finshes without
errors, therefore if I was to go all the way through the intervals of
00:00-24:00 I would end up losing the 24:00 interval PLEASE HELP!!! e-
mail me at if you want


Sub AnsweredMath()

Dim TimeInv
Dim cnt As Long
Dim total As Long


'sort the data
Range("A1").Activate
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

'Start at the second row - assumes will always be the first time
interval
Range("A1").Activate
TimeInv = ActiveCell.Value 'get the first range in the sheet;
assumes info starts at A1

While ActiveCell < "" 'while the current cell is not blank -
processes until it hits a blank cell

If ActiveCell.Value < TimeInv Then
ActiveCell.EntireRow.Insert 'insert a row and write out
values
ActiveCell.Value = TimeInv
ActiveCell.Offset(0, 1).Activate
ActiveCell.Offset.Value = total
total = 0 'reset total
cnt = 0 'reset cnt
ActiveCell.Offset(1, -1).Select 'go to the next row
TimeInv = ActiveCell.Value
Else
'capture count in varibles, then delete the row
cnt = ActiveCell.Offset(0, 1).Value
total = cnt + total
ActiveCell.EntireRow.Delete
End If

Wend

End Sub

HELP!! Macro Deletes Last Interval
 

On Dec 9, 8:34*pm, JLGWhiz wrote:
If I recall correctly, the twentyfour hour clock goes from 00:00 to 23:59.. *
There is no 24:00. *00:00 = Midnight, 12:00 = Noon.



" wrote:
Ok,

I have a macro that adds column A and B for sequential time intervals
(Its confusing see the example)

IE:

Before the Macro is Ran;

column A * * *column B

05:00 * * * * * * *1
05:00 * * * * * * *1
05:00 * * * * * * *0

After the Macro is Ranl

column A * * *column B

05:00 * * * * * * *2

Therefore it takes the numbers in column B and adds them to the time
interval and creates a SUM...the problem with the macro is when it
gets to the last set of intervals it deletes it and finshes without
errors, therefore if I was to go all the way through the intervals of
00:00-24:00 I would end up losing the 24:00 interval PLEASE HELP!!! e-
mail me at if you want

Sub AnsweredMath()

Dim TimeInv
Dim cnt As Long
Dim total As Long

'sort the data
Range("A1").Activate
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

'Start at the second row - assumes will always be the first time
interval
Range("A1").Activate
TimeInv = ActiveCell.Value * 'get the first range in the sheet;
assumes info starts at A1

While ActiveCell < "" *'while the current cell is not blank -
processes until it hits a blank cell

* * If ActiveCell.Value < TimeInv Then
* * * * * * ActiveCell.EntireRow.Insert *'insert a row and write out
values
* * * * * * ActiveCell.Value = TimeInv
* * * * * * ActiveCell.Offset(0, 1).Activate
* * * * * * ActiveCell.Offset.Value = total
* * * * * * total = 0 'reset total
* * * * * * cnt = 0 'reset cnt
* * * * * * ActiveCell.Offset(1, -1).Select 'go to the next row
* * * * * * TimeInv = ActiveCell.Value
* * * * Else
* * * * * * 'capture count in varibles, then delete the row
* * * * * * cnt = ActiveCell.Offset(0, 1).Value
* * * * * * total = cnt + total
* * * * * * ActiveCell.EntireRow.Delete
* * End If

Wend

End Sub- Hide quoted text -

- Show quoted text -

That is correct..I suppose I should have said untill 23:59 however, it
is doesnt matter for this instance because I do not ever even make it
to 23:59 for it to matter. If this macro is ran for any amount of
intervals, say the intervals are in 15 minute blocks from 00:15-10:00
it will not display the 10:00 interval.

HELP!! Macro Deletes Last Interval
 

Hi courtnml,

You need to do a few things to make this code work the way you want.
1. Change "While ActiveCell < """t to "Do Until ActiveCell = """
2. Change "Wend" to "Loop"
3. Add the following lines after Loop and before End Sub

ActiveCell.Value = TimeInv
ActiveCell.Offset(0, 1).Activate
ActiveCell.Offset.Value = total

Hong Quach

HELP!! Macro Deletes Last Interval
 

On Dec 9, 10:44*pm, Hong Quach
wrote:
Hi courtnml,

You need to do a few things to make this code work the way you want.
1. *Change "While ActiveCell < """t to "Do Until ActiveCell = """
2. *Change "Wend" to "Loop"
3. *Add the following lines after Loop and before End Sub

ActiveCell.Value = TimeInv
ActiveCell.Offset(0, 1).Activate
ActiveCell.Offset.Value = total

Hong Quach


Checking this out, it looks like its what I am missing for sure.
Thanks in advance Hong!