Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get Info on One Line (Excel 97 SR2)

    I am trying to use formulas to extract information from a table in the attached spreadsheet. I need to get the activity, cost center and amount on one line in separate cells. The actual report has a lot more info than I am showing here.

    I could just continue what I'm doing and then sort the columns but I will need to do this every month when we are in a rush.

    Ideally, I would write a macro that would extract the information to a new location or a new tab, but I haven
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Get Info on One Line (Excel 97 SR2)

    I had a quick look.

    My attachment has a macro which may do the trick.
    Shortcut key assigned - Press [Ctrl][Shift][Z]

    zeddy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get Info on One Line (Excel 97 SR2)

    Here is another macro. It assumes the"input" is on sheet1 and will send the output to sheet2. Change as appropriate

    Steve
    <pre>Option Explicit

    Sub SobersheExtract()
    Dim shtIn As Worksheet
    Dim shtOut As Worksheet
    Dim rCell As Range
    Dim rng As Range
    Dim x As Integer

    Set shtIn = Worksheets("sheet1")
    Set shtOut = Worksheets("sheet2")
    shtIn.Select
    Set rng = shtIn.Range(Range("a1"), Range("A65536").End(xlUp))

    shtOut.Cells.ClearContents

    shtOut.Cells(1, 1).Value = "Activity"
    shtOut.Cells(1, 2).Value = "Cost Center"
    shtOut.Cells(1, 3).Value = "Amount"

    x = 2
    For Each rCell In rng
    If Left(rCell.Value, 10) = "DEPARTMENT" Then
    shtOut.Cells(x, 1).Value = Right(rCell.Value, 3)
    shtOut.Cells(x, 2).Value = Mid(rCell.Value, 14, 5)
    shtOut.Cells(x, 3).Value = rCell.Offset(2, 7).Value
    x = x + 1
    End If
    Next rCell
    End Sub
    </pre>


  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Info on One Line (Excel 97 SR2)

    It works great. Thank You. I never used Advanced Filter before so can you explain what the following portion of your macro is doing?

    Sheets(zSource).Select
    [h4] = "xx" ......................
    I understand this is making a header row for the data filter
    [L4] = "zz"
    Sheets(zNew).Select
    Set zData = Sheets(zSource).[h4].CurrentRegion ..............
    Here you are selecting the "current region" of the header
    zData.AdvancedFilter Action:=xlFilterCopy, _ ................. The next 3 lines are the part I don't understand
    CriteriaRange:=Range("A1:C2"), _
    CopyToRange:=Range("A4:C4"), Unique:=False


    Thanks Again!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Info on One Line (Excel 97 SR2)

    This macro is great too! Using this one I don't have to do any formulas. Awesome.

    Thank You! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    PS. The Lounge always comes through!!!!!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •