How Do I Automatically Populate These Fields in XL2003?

[ZHammer]

Hello!

System Info: OS - Windows XP, Excel Version - 2003

I am wanting to see if someone out there might be willing to guide me through the steps of doing something. It requires a bit of explanation and details, so bear with me.

GOAL: Transfer specific pieces of information from one xl sheet to a more "workable" format in another sheet.

Part of my job has me "grade" these tour reports. Grading consists of these things: transferring information about each tour (e.g. - Name of officer, tour number, docket unit used, and any errors) to various sheets that are used as summaries when the tours are audited. Grading also involves checking any errors against information detailed in each person personal shift report - which would indicate that the error should or could be "excused" (such as, for a time error due to a restroom break).

Currently, I manually input the information I need into a "notes" xl file - then I transfer that information to the various places it needs to go. I use this notes file because it is easier to keep track of this information in one place.

Here is an example of the tour report:
Example Tour Report in Google Docs
Some Notes:

• some of the formatting doesn't transfer as well when uploaded to google docs, but for the sake of reference it should work alright
• I have changed some of the information so that it doesn't reveal details about specific locations, business names, or employee names involved - places where changes have been made will have green text

Here is an example of the notes sheet I keep:
Example of Notes sheet in Google Docs

Here is where I specifically get the information I transfer - I will give you details based on the line it is on:
All these are in reference to the first sheet of the Example sheet
Line 13: I transfer the tour number - 3
Line 14: I transfer the start time (in military time) - 00:14
Line 14: I transfer the date - 7/29/10
Line 16: I transfer the employee first name into one column last name into another - First: Example ; Last: Name

This pattern would then continue at lines 68-72, lines 124-128, etc...

The things I just listed are what I would like to automate most.

Secondarily, I would also like the following to happen:
If you look at line 99 - you will see it is highlighted in yellow and that there is also a note in red in column E - this happens automatically whenever there is a time error (i.e. - time between two dockets is greater than 10m 59s). If I could - I would like to have these errors automatically called to may attention along with the other information I use for notes so that I can quickly locate the errors to see if they are excusable or not.


I don't know if this is doable, simple, difficult, or something else - so please let me know if I am asking "too much". I am very grateful that you have even read this far If you can offer any further help with this I would be very grateful for that as well.

Let me know if you need me to clarify anything - I tried to be as specific as I could - but this was kind of hard to put into words.

Thanks very much in advance!

 

[BrianB]

cannot see the data in the lines you refer to

 

[ZHammer]

Try it out again, I made it so that you can edit it - so you should be able to double click to see what is in the boxes there. Unfortunately google docs doesn't overlap the text like excel does - but this should allow you to be able to see what is inside.

Thanks for taking a look

 

[BrianB]

I was able to expand the column and copy/paste to Excel.
Google docs only allows copying of 1000 cells, but that gave no problems.
Cannot see where Unit comes from.

'========================================================================================
'- SUMMARISE DATA FROM REPORT COLUMN A TO WORKSHEET
'- Run from data sheet. Transfers to worksheet called "Summary"
'- Uses 'Find' to locate start of each block, checks for errors between.
'- Brian Baulsom August 2010
'========================================================================================
Sub GET_DATA()
    Dim FromSheet As Worksheet
    Dim FromRow As Long
    '-----------------------------------------------------
    Dim MyFind As String        ' find beginning of data
    Dim FoundCell As Range
    Dim Began As Variant
    Dim MyName As String
    Dim sp As Integer           ' space position in MyName
    Dim Tour As String
    Dim Unit
    '-----------------------------------------------------
    Dim ErrorRow As Long        ' to check errors
    Dim ErrorCount As Integer
    Dim ErrorText As String
    Dim ErrorInfo As String
    Dim rw As Long
    '------------------------------------------------------
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    '-----------------------------------------------------------------------------------
    '- INITIALISE
    MyFind = "TOUR No."
    Set FromSheet = ActiveSheet
    Set ToSheet = Worksheets("Summary")
    ToRow = 2
    ErrorRow = 2
    Application.Calculation = xlCalculationManual
    '===================================================================================
    '- GET DATA
    '===================================================================================
    Set FoundCell = FromSheet.Columns("A:A").Cells.Find(What:=MyFind)
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        Do
            FromRow = FoundCell.Row
            Application.StatusBar = "Processing row : " & FromRow
            With FromSheet
                '------------------------------------------------------------------------
                '- DATE & TIME
                Began = .Cells(FromRow + 1, "A").Value
                Began = Right(Began, Len(Began) - 12)
                '- NAME
                MyName = .Cells(FromRow + 4, "A").Value
                MyName = Trim(Right(MyName, Len(MyName) - 23))
                sp = InStr(1, MyName, " ", vbTextCompare)   ' find space in name
                '- TOUR
                Tour = Trim(.Cells(FromRow, "A").Value)
                Tour = Right(Tour, Len(Tour) - InStr(1, Tour, "#", vbTextCompare))
                '------------------------------------------------------------------------
            End With
            '===========================================================================
            '- CHECK FOR ERRORS (COLUMN E)
            '===========================================================================
            ErrorCount = 0
            ErrorInfo = ""
            If ErrorRow <> FromRow Then
                For rw = ErrorRow To FromRow
                    ErrorText = Trim(FromSheet.Cells(rw, "E").Value)
                    If ErrorText <> "" Then
                        ErrorCount = ErrorCount + 1
                        ErrorInfo = ErrorInfo & "[" & FromSheet.Cells(rw, "A").Value _
                            & ":" & ErrorText & "]"
                    End If
                Next
                ErrorRow = FromRow
            End If
            '===========================================================================
            '- DATA TO SUMMARY
            '===========================================================================
            With ToSheet
                .Cells(ToRow, "A").Value = DateValue(Began)
                .Cells(ToRow, "B").Value = TimeValue(Began)
                .Cells(ToRow, "C").Value = Left(MyName, sp - 1)
                .Cells(ToRow, "D").Value = Right(MyName, Len(MyName) - sp)
                .Cells(ToRow, "E").Value = Tour
                .Cells(ToRow, "G").Value = ErrorCount
                .Cells(ToRow, "H").Value = ErrorInfo
            End With
            '===========================================================================
            '- NEXT DATA BLOCK
            ToRow = ToRow + 1
            Set FoundCell = FromSheet.Cells.FindNext(FoundCell)
        Loop While Not FoundCell Is Nothing _
            And FoundCell.Address <> FirstAddress
    End If
    '------------------------------------------------------------------------------------
    '- FORMAT DATE & TIME COLUMNS
    With ToSheet
        .Range(.Cells(2, "A"), .Cells(ToRow, "A")).NumberFormat = "m/dd/yyyy"
        .Range(.Cells(2, "B"), .Cells(ToRow, "B")).NumberFormat = "hh:mm"
    End With
    '-------------------------------------------------------------------------------------
    MsgBox ("Done")
    Application.Calculation = xlCalculationAutomatic
    Application.StatuBar=False
End Sub
'========================================================================================

 

[ZHammer]

Hrm... it looks like I forgot to even post that part - sorry about that!

Here's where I get the unit number from:
Line 16 says: "RECORDER No. :84657"
The last two digits in that number are the unit number - in this case 57.

In the actual excel spreadsheet it has those last two digits highlighted red.

Don't know why I didn't do this before... but here is a google doc link where you can download the actual excel files - rather than the google modified ones.
Notes: https://docs.google.com/leaf?id=0B8...IxMjAtZTBhMjhhY2QzMWFh&hl=en&authkey=CN26wsQB

Example Log: https://docs.google.com/leaf?id=0B8...ZWVkNzJkNmE4YmVj&sort=name&layout=list&num=50

Hopefully that is more helpful.

Thanks for the help!

 

[BrianB]

Presumably you have figured this out. Something like

                '- UNIT
                Unit = Trim(.Cells(FromRow + 3, "A").Value)
                Unit = Right(Unit, 2)
                ToSheet.Cells(ToRow, "F").Value = Unit