How to find a specific inform with some cells and return total

S

sean

Please help!!! Below are parts of the exact inform that
on a weekly base i will be running a recorded macro
to filter a team schedule. The schedules are grouped per
day. but i will be running the macro per week. I will
like help with knowing what macro I could add into my
recorded macro that will look for a specific activity and
record its
total (eg. the sum hours and mins of breaks)on a cell
that i will insert just above the total cell.I will need
macro or fomula
to report the total breaks on cell D26. i will be using
this marco for other teams that have different breaks or
other
activities. I will greatly appreciate any help and thanks
in advance.
This is the recorded macro:
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1,
Criteria1:="====================="
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="-----------
---------"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="----- ----
- -----"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="--:-- --:-
- --:--"
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=1, Criteria1:="Report
Across Agent Mo"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Sorted by:
Name Activ"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Meeting"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Logon"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Lunch"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Break"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Logoff"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="Da"
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=3, Criteria1:="Da"
Range("C18:C159").Select
Selection.ClearContents
Range("A18:A207").Select
Selection.ClearContents
Columns("D:D").Select
Selection.Replace What:="te", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=1, Criteria1:="Scheduled"
Selection.AutoFilter Field:=1, Criteria1:="-----
Scheduled-----"
Selection.AutoFilter Field:=1, Criteria1:="Scheduled"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Activities"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="e
____________________"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Selection.AutoFilter Field:=5, Criteria1:="="
Rows("2:206").Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=5, Criteria1:="ence ------
-- ---"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Total"
Range("D7:E105").Select
Selection.ClearContents
Range("C105").Select
Selection.ClearContents
ActiveSheet.ShowAllData
Columns("A:E").Select
ActiveWindow.SmallScroll Down:=-6
Selection.AutoFilter Field:=1, Criteria1:="MU: 19
WDTS-W Mobile"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Shift: 0
All Day"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="From:
01/04/04"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="To:
01/10/04"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Printed:
17:28 01/15/0"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=4,
Criteria1:="Supervisor: Ken"
Selection.ClearContents
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Selection.AutoFilter Field:=5, Criteria1:="="
Rows("6:206").Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=2, Criteria1:="--:-- --:-
-"
Selection.AutoFilter Field:=3, Criteria1:="--:--"
Range("B11:C83").Select
Selection.ClearContents
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=1, Criteria1:="--:-- --:-
- --:--"
Range("A10:A81").Select
Selection.ClearContents
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=3, Criteria1:="------"
Range("C2:C68").Select
Selection.ClearContents
ActiveSheet.ShowAllData
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Copy
Columns("C:C").Select
ActiveSheet.Paste
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B4").Select
ActiveWindow.SmallScroll Down:=54
Range("D86:E86").Select
Selection.ClearContents
Range("B86").Select
ActiveCell.FormulaR1C1 = "21023 JOHN"
Range("B84").Select
ActiveCell.FormulaR1C1 = "21023 JOHN"
Range("F75").Select
ActiveWindow.SmallScroll Down:=-9
Range("B65").Select
ActiveCell.FormulaR1C1 = "21023 JOHN"
Range("D62").Select
ActiveWindow.SmallScroll Down:=-15
Range("B45").Select
ActiveCell.FormulaR1C1 = "21023 JOHN"
Range("D47").Select
ActiveWindow.SmallScroll Down:=-45
Rows("25:25").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("B26").Select
ActiveCell.FormulaR1C1 = "LUNCH"
Range("D26").Select
ActiveCell.FormulaR1C1 = "?"
Range("F25").Select
ActiveWindow.SmallScroll Down:=18
Rows("48:48").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("B49").Select
ActiveCell.FormulaR1C1 = "LUNCH"
Range("D49").Select
ActiveCell.FormulaR1C1 = "?"
Range("B48").Select
ActiveCell.FormulaR1C1 = "BREAKS"
Range("D48").Select
ActiveCell.FormulaR1C1 = "?"
Range("G46").Select
ActiveWindow.SmallScroll Down:=-27
End Sub

This is the excel sheet:

A1 B1 C1 D1 E1
: 01/06/04
Scheduled -----Scheduled----- Actual ------
Actual-
Activity From To Dur. Activity
From To Dur.
Logon 11:59 13:41 1:42
Open Time 12:00 13:45 01:45
Break 13:41 13:56 0:15
Break 13:45 14:00 00:15
Logon 13:56 15:59 2:03
Open Time 14:00 16:00 02:00
Lunch 15:59 16:58 0:59
Lunch 16:00 17:00 01:00
Logon 16:58 19:01 2:03
Open Time 17:00 19:00 02:00
Break 19:00 19:15 00:15
Break 19:01 19:15 0:14
Open Time 19:15 21:00 01:45 Logon 19:15
21:00 1:45
Overtime-Open 21:00 21:19 00:19 Logon 21:00
21:19 0:19


LUNCH ?
Total 9:19
: 01/07/04
Scheduled -----Scheduled----- Actual ------
Actual-
Activity From To Dur. Activity
From To Dur.
Logon 11:59 13:57 1:58
Open Time 12:00 14:00 02:00
Break 13:57 14:11 0:14
Break 14:00 14:15 00:15
Logon 14:11 16:49 2:38
Open Time 14:15 16:00 01:45
Lunch OT 16:00 16:30 00:30
Lunch 16:30 17:00 00:30
Lunch 16:49 17:19 0:30
Open Time 17:00 18:45 01:45
Logon 17:19 18:49 1:30
Break 18:45 19:00 00:15
Break 18:49 19:02 0:13
Open Time 19:00 21:00 02:00
Logon 19:02 21:07 2:05
Overtime-Open 21:00 21:07 00:07

BREAKS ?
LUNCH ?
Total 9:07
: 01/08/04
Scheduled -----Scheduled----- Actual ------
Actual-
Activity From To Dur. Activity
From To Dur.
Logon 11:59 13:54 1:55
Open Time 12:00 14:00 02:00
Break 13:54 14:09 0:15
Break 14:00 14:15 00:15
Logon 14:09 14:55 0:46
Open Time 14:15 14:55 00:40
Team Meeting/Brief 14:55 16:32 01:37 Meeting
14:55 16:32 1:37
Lunch 16:32 17:32 01:00 Lunch 16:32 17:31
0:59
Logon 17:31 18:52 1:21
Open Time 17:32 19:00 01:28
Break 18:52 19:03 0:11
Break 19:00 19:15 00:15
Logon 19:03 21:12 2:09
Open Time 19:15 21:00 01:45
Overtime-Open 21:00 21:12 00:12

Total 9:12
: 01/09/04
Scheduled -----Scheduled----- Actual ------
Actual-
Activity From To Dur. Activity
From To Dur.
Logon 11:59 13:48 1:49
Open Time 12:00 13:45 01:45
Break 13:45 14:00 00:15
Break 13:48 14:01 0:13
Open Time 14:00 16:08 02:08
Logon 14:01 16:08 2:07
Lunch 16:08 16:38 00:30 Lunch 16:08 16:38
0:30
Lunch OT 16:38 17:08 00:30 Logon 16:38
17:08 0:30
Open Time 17:08 18:45 01:37 Logon 17:08
18:43 1:35
Break 18:43 18:54 0:11
Break 18:45 19:00 00:15
Logon 18:54 21:16 2:22
Open Time 19:00 21:00 02:00
Overtime-Open 21:00 21:16 00:16

Total 9:16
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro code 0
Record Click on Error Indicator 2
Autofil on variable rows, delete extract and show remaining rows 3
Copy and paste area are not the same shape error after AutoFilter 0
can anybody speed up this macro 2
Autofilter used programmaticaly causes error 2
Problem formatting dates from CSV file 2
Macro to delete with filters 4

Top