HELP!! Macro Deletes Last Interval

[courtnml]

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 (e-mail address removed) 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

 

[JLGWhiz]

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.

 

[courtnml]

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.


















- 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.

 

[Hong Quach]

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

 

[courtnml]

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!