Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro deletes row in range, macro then skips the row moved up | Excel Discussion (Misc queries) | |||
Macro that deletes every third row....+ | Excel Discussion (Misc queries) | |||
Macro that deletes certain rows only | Excel Programming | |||
Macro That Deletes Columns | Excel Programming | |||
Macro that adds then deletes | Excel Programming |