Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search .xls sheet for keyword, move row to diff sh (Excel 2003 sp3)

    I've got some code that will search a "cell", then strip out a portion of the row (pasting a copy to a new sheet) - however, someone is asking for a different variation .
    -- need help editing the code to make it work....

    There's 2 sheets: "DATA" (holds table of data) and OUTPUT (this is where the copied data is pasted to)
    ** Need code to start with row 2 (cell A2) and scan across and down every row through to end....
    ** Searching for keyword: "tube"
    ** IF any cells within the entire row holds a refc of "tube"
    ** THEN copy/paste THAT row to the OUTPUT sheet.

    The below is similar code- but needs edited to perform the above - and I'm not sure how to edit it to make it work...
    Please edit SAMPLE FILE (attached) as you see needed... Thanks SO MUCH! C.

    If you can make it to where the word "tube" is NOT CASE SENSITIVE - that would be excellent! (any version of word "tube" would be great! "Tube, tube, TUBE, tubing, Tubing,") using first 3 chars...
    If not, I'll go with anything you can provide....

    ============================================
    Sub MoveIt()
    Dim Lastrow As Long, r As Integer, c As Integer
    Lastrow = Sheets("Data").Range("A65536").End(xlUp).Row
    c = 1
    For r = 1 To Lastrow
    If Sheets("Data").Cells(r, 1) = "A" Then
    Sheets("Output").Cells(c, 1) = Sheets("Data").Cells(r, 3).Value
    Sheets("Output").Cells(c, 2) = Sheets("Data").Cells(r, 4).Value
    c = c + 1
    End If
    Next r
    Sheets("Output").Range("A1").Select
    End Sub
    ============================================
    Attached Files Attached Files

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

    Re: Search .xls sheet for keyword, move row to diff sh (Excel 2003 sp3)

    Welcome to Woody's Lounge!

    Try this version of the code:
    <code>
    Sub MoveIt()
    Dim LastRow As Long
    Dim r As Long
    Dim s As Long
    Dim oCell As Range
    LastRow = Sheets("Master").Range("A65536").End(xlUp).Row
    For r = 1 To LastRow
    ' Search for the word "tub" in the r-th row
    Set oCell = Sheets("Master").Rows®.Find(What:="tub", _
    LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
    If Not oCell Is Nothing Then
    ' If found, increase target row number
    s = s + 1
    ' And copy source row to target row
    Worksheets("Master").Rows®.Copy _
    Destination:=Worksheets("Output").Range("A" & s)
    End If
    Next r
    End Sub
    </code>
    (Your source sheet is named Master, not Data)

  3. #3
    New Lounger
    Join Date
    Jan 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search .xls sheet for keyword, move row to diff sh (Excel 2003 sp3)

    It works beautifully!!! Thanks a million!

Posting Permissions

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