Search in macro (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

Try

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

Reply | Quote

Wrong answer Hans!

jha’s question was [indent]

---

How do i write a macro …

---

[/indent]Your reply showed how Hans would write such a macro.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

>Wrong answer Hans!
Wrong answer Macropod!
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

• Tools, Macro, Record
• Turn on relative addressing
• Perform the steps for a found “xxx” value (copy, move across/up/down/paste
• Tools, Macro, stop Recording[/list]Then edit the macro to insert the test for “xxx”

Reply | Quote

Slight improvement to avoid error:

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

Reply | Quote