Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
I need to process an Excel file. I want to run a macro. The macro will not
know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Try this
Public Sub ProcessData() Const TEST_COLUMN As String = "E" '<=== change to suit Dim i As Long Dim LastRow As Long Dim StartAt As Long Dim sh As Worksheet With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row i = 1 Do Until .Cells(i, TEST_COLUMN).Value < "N" Or _ i LastRow i = i + 1 Loop If i < LastRow Then StartAt = i Do Until .Cells(i, TEST_COLUMN).Value = "N" Or _ i LastRow i = i + 1 Loop If i <= LastRow Then MsgBox Format(.Cells(i, TEST_COLUMN).Offset(0, 1).Value - _ .Cells(StartAt, TEST_COLUMN).Offset(0, 1).Value, "h:mm") End If End If End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nils Titley" wrote in message ... I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Here is a macro that should do what you want.
Sub CountEtc() Dim LR As Long Dim Rcount As Long, Lcount As Long Dim FirstN As Date, SecondN As Date Dim CurrChar As String, NextChar As String 'find last row LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'initialize variables Rcount = 0 Lcount = 0 'walk down column A Range("A1").Activate Do While ActiveCell.Row <= LR CurrChar$ = UCase(ActiveCell.Offset(0, 4).Value) NextChar$ = UCase(ActiveCell.Offset(1, 4).Value) Select Case CurrChar$ Case "N" If (NextChar$ = "L") Or (NextChar$ = "R") Then FirstN = ActiveCell.Value End If Case "L" Lcount = Lcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If Case "R" Rcount = Rcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If End Select ActiveCell.Offset(1, 0).Activate Loop ActiveCell.Offset(1, 0).Activate ActiveCell.Value = "TOTAL there are " & Lcount & " L and " & _ Rcount & " R and time is " & Format((SecondN - FirstN), "h:mm") End Sub Hope this helps, Hutch "Nils Titley" wrote: I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Bob,
I like your routine. The counting is correct but the time is not working correctly. The results for the time is 0:42. The first value that it should use is 6:06 and the last value it should use is 11:18. So the time result will be 5:12. It is a 24hr clock. I add another "h" to the format. I a bit rusty on my VB so I appreciate your input. Thanks Nils "Bob Phillips" wrote: Try this Public Sub ProcessData() Const TEST_COLUMN As String = "E" '<=== change to suit Dim i As Long Dim LastRow As Long Dim StartAt As Long Dim sh As Worksheet With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row i = 1 Do Until .Cells(i, TEST_COLUMN).Value < "N" Or _ i LastRow i = i + 1 Loop If i < LastRow Then StartAt = i Do Until .Cells(i, TEST_COLUMN).Value = "N" Or _ i LastRow i = i + 1 Loop If i <= LastRow Then MsgBox Format(.Cells(i, TEST_COLUMN).Offset(0, 1).Value - _ .Cells(StartAt, TEST_COLUMN).Offset(0, 1).Value, "h:mm") End If End If End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nils Titley" wrote in message ... I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Bob
It appears that your routine is working correctly but there is an error in the data. I have to check on some thing. I will leave another message. Thanks Nils "Nils Titley" wrote: Bob, I like your routine. The counting is correct but the time is not working correctly. The results for the time is 0:42. The first value that it should use is 6:06 and the last value it should use is 11:18. So the time result will be 5:12. It is a 24hr clock. I add another "h" to the format. I a bit rusty on my VB so I appreciate your input. Thanks Nils "Bob Phillips" wrote: Try this Public Sub ProcessData() Const TEST_COLUMN As String = "E" '<=== change to suit Dim i As Long Dim LastRow As Long Dim StartAt As Long Dim sh As Worksheet With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row i = 1 Do Until .Cells(i, TEST_COLUMN).Value < "N" Or _ i LastRow i = i + 1 Loop If i < LastRow Then StartAt = i Do Until .Cells(i, TEST_COLUMN).Value = "N" Or _ i LastRow i = i + 1 Loop If i <= LastRow Then MsgBox Format(.Cells(i, TEST_COLUMN).Offset(0, 1).Value - _ .Cells(StartAt, TEST_COLUMN).Offset(0, 1).Value, "h:mm") End If End If End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nils Titley" wrote in message ... I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Tom,
I used your routine after modifing it based on some information. I have another question about the hours and minutes. Once I have the total time, I have to convert it to minutes so I can use that number to divided into the total L & Rs. I tried stripping the Hours and multiplying by 60 and adding the minutes but the calculations are not coming out correctly. I also learned that the data has a separate field for time. Thanks for your help. Nils "Tom Hutchins" wrote: Here is a macro that should do what you want. Sub CountEtc() Dim LR As Long Dim Rcount As Long, Lcount As Long Dim FirstN As Date, SecondN As Date Dim CurrChar As String, NextChar As String 'find last row LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'initialize variables Rcount = 0 Lcount = 0 'walk down column A Range("A1").Activate Do While ActiveCell.Row <= LR CurrChar$ = UCase(ActiveCell.Offset(0, 4).Value) NextChar$ = UCase(ActiveCell.Offset(1, 4).Value) Select Case CurrChar$ Case "N" If (NextChar$ = "L") Or (NextChar$ = "R") Then FirstN = ActiveCell.Value End If Case "L" Lcount = Lcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If Case "R" Rcount = Rcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If End Select ActiveCell.Offset(1, 0).Activate Loop ActiveCell.Offset(1, 0).Activate ActiveCell.Value = "TOTAL there are " & Lcount & " L and " & _ Rcount & " R and time is " & Format((SecondN - FirstN), "h:mm") End Sub Hope this helps, Hutch "Nils Titley" wrote: I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Converting the times to a minutes integer is tricky. The best way I have
found is to use the Excel MINUTE function, which is not readily available to VBA. Would the following work for you? Replace the ActiveCell.Value= statement at the end of the macro with this code: ActiveCell.Value = "TOTAL there are " & Lcount & " L and " & _ Rcount & " R and time is " ActiveCell.Offset(0, 4).Formula = _ "=MINUTE(""" & SecondN & """-""" & FirstN & """)" ActiveCell.Offset(0, 5).Value = "minutes" Hope this helps, Hutch "Nils Titley" wrote: Tom, I used your routine after modifing it based on some information. I have another question about the hours and minutes. Once I have the total time, I have to convert it to minutes so I can use that number to divided into the total L & Rs. I tried stripping the Hours and multiplying by 60 and adding the minutes but the calculations are not coming out correctly. I also learned that the data has a separate field for time. Thanks for your help. Nils "Tom Hutchins" wrote: Here is a macro that should do what you want. Sub CountEtc() Dim LR As Long Dim Rcount As Long, Lcount As Long Dim FirstN As Date, SecondN As Date Dim CurrChar As String, NextChar As String 'find last row LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'initialize variables Rcount = 0 Lcount = 0 'walk down column A Range("A1").Activate Do While ActiveCell.Row <= LR CurrChar$ = UCase(ActiveCell.Offset(0, 4).Value) NextChar$ = UCase(ActiveCell.Offset(1, 4).Value) Select Case CurrChar$ Case "N" If (NextChar$ = "L") Or (NextChar$ = "R") Then FirstN = ActiveCell.Value End If Case "L" Lcount = Lcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If Case "R" Rcount = Rcount + 1 If NextChar$ = "N" Then SecondN = ActiveCell.Offset(1, 0).Value End If End Select ActiveCell.Offset(1, 0).Activate Loop ActiveCell.Offset(1, 0).Activate ActiveCell.Value = "TOTAL there are " & Lcount & " L and " & _ Rcount & " R and time is " & Format((SecondN - FirstN), "h:mm") End Sub Hope this helps, Hutch "Nils Titley" wrote: I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
"Tom Hutchins" wrote in message ... Converting the times to a minutes integer is tricky. The best way I have found is to use the Excel MINUTE function, which is not readily available to VBA. msgbox minute(now) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Help
Whoops... I stand corrected. Thanks, Bob. I had tried calling it using
Application.WorksheetFunction and saw it's not available there. I guess that's because it's already available as a native VBA function. Doesn't affect the code I provided to Nils - it should work fine as written. Hutch "Bob Phillips" wrote: "Tom Hutchins" wrote in message ... Converting the times to a minutes integer is tricky. The best way I have found is to use the Excel MINUTE function, which is not readily available to VBA. msgbox minute(now) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid Outlook macro security for Excel bades macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |