Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search in macro (Excel 2003)

    How do i write a macro to search through an Excel file for "xxx" in COL A, move over to COL D and move the cell in D and the cell underneath it down 2 rows, go back up 2 rows and delete 2 rows and then continue? Thanks for the help.

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

    Re: Search in macro (Excel 2003)

    Try
    <code>
    Sub TestCode()
    Dim oCell As Range
    Set oCell = Range("A:A").Find(What:="xxx", _
    LookIn:=xlValues, LookAt:=xlWhole)
    Do While Not oCell Is Nothing
    oCell.Offset(0, 3).Resize(2, 1).Copy _
    Destination:=oCell.Offset(2, 3)
    oCell.Resize(2, 1).EntireRow.Delete
    Set oCell = Range("A:A").FindNext
    Loop
    End Sub</code>

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Search in macro (Excel 2003)

    Wrong answer Hans!

    jha's question was
    <hr>How do i write a macro ...<hr>
    Your reply showed how Hans would write such a macro.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search in macro (Excel 2003)

    Slight improvement to avoid error:

    <pre>Sub TestCode()
    Dim oCell As Range
    On Error Resume Next
    Set oCell = Range("A:A").Find(What:="xxx", _
    LookIn:=xlValues, LookAt:=xlWhole)
    Do While Not oCell Is Nothing
    oCell.Offset(0, 3).Resize(2, 1).Copy _
    Destination:=oCell.Offset(2, 3)
    oCell.Resize(2, 1).EntireRow.Delete
    Set oCell = Range("A:A").FindNext
    Loop
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search in macro (Excel 2003)

    >Wrong answer Hans!
    Wrong answer Macropod! <img src=/S/hello.gif border=0 alt=hello width=25 height=29>
    jha's question was
    How do i write a macro ...
    Obviously, he doesn't, otherwise he wouldn't be asking.

    JHA: I suspect a better answer might be
    <UL><LI>Tools, Macro, Record
    <LI>Turn on relative addressing
    <LI>Perform the steps for a found "xxx" value (copy, move across/up/down/paste
    <LI>Tools, Macro, stop Recording[/list]Then edit the macro to insert the test for "xxx"

Posting Permissions

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