Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find text until last occurrence (2003 SP3)

    I am finding certain text (the word "Total") then correcting text in nearby cells and copy and pasting those few cells to a new list at top right of the same sheet. This works OK but I need it to repeat the find, correct and paste to bottom of the new list until there are no more occurrences of the find word in the cells below. Help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find text until last occurrence (2003 SP3)

    Welcome to Woody's Lounge!

    If you would like to automate this by using a macro, could you provide details - which cells, what is "correcting text in nearby cells", which cells should be copied, etc.? Please try to give sufficient, clear and precise information.

  3. #3
    New Lounger
    Join Date
    Feb 2008
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find text until last occurrence (2003 SP3)

    Thanks for your quick response. Sample Excel file attached with the macros I have managed to do and want to streamline. This data comes monthly from another system to Excel and then I am importing the cleaned up list into an Access table. I am only extracting the bits from the Excel file that I need for Access. Thanks again.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find text until last occurrence (2003 SP3)

    The following macro combines the two you had and loops until "Total " is not found any more.
    <code>
    Sub FillTable()
    Dim oCell As Range

    Range("L:P").ClearContents
    Range("L1") = "Mo"
    Range("M1") = "Yr"
    Range("N1") = "MerchantNo"
    Range("O1") = "TRANX"
    Range("P1") = "Value"

    Set oCell = Range("A:A").Find(What:="Total ", _
    LookIn:=xlValues, LookAt:=xlPart)
    Do While Not oCell Is Nothing
    oCell = "Total"
    oCell.Offset(0, 2) = Mid(oCell.Offset(-1, 3), 5, 2)
    oCell.Offset(0, 3) = Left(oCell.Offset(-1, 3), 4)
    oCell.Offset(0, 4) = oCell.Offset(-1, 4)
    oCell.Offset(0, 2).Resize(1, 5).Copy _
    Destination:=Range("L65536").End(xlUp).Offset(1, 0)
    Set oCell = Range("A:A").FindNext(After:=oCell)
    Loop
    End Sub
    </code>
    As you see, the code doesn't select any cells, and it sets the values directly instead of using formulas and replacing them with their values.
    If you only need to create the table in columns L through P, the code could be simplified a bit further.

  5. #5
    New Lounger
    Join Date
    Feb 2008
    Location
    Sydney, New South Wales, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find text until last occurrence (2003 SP3)

    Wow! I want to be clever like you! Thanks.

Posting Permissions

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