Excel Macro Help

[Nils Titley]

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.

 

[Bob Phillips]

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)

 

[Tom Hutchins]

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]

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

 

[Nils Titley]

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]

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]

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

 

[Bob Phillips]

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)

 

[Tom Hutchins]

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