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