Search For A String. Then Copy All Cells Between Each Occurrence

  • Hello!


    I'm just new here as well as in Excel-VBA stuffs. I will appreciate if someone could help me. Here is the scenario. I have a data piled in a single column and different rows. Like this one:


    spot.return
    23,54,56..
    532,677,755...
    ..
    ..
    ..
    876,989,999;
    spot.return
    54,58,60..
    ..
    ..
    spot.return


    I need to select and copy all rows in between the occurence of "spot.return". There are a total of 80 occurences of "spot.return".


    Thanks and Regards,
    khalel04 :)

  • Re: Search For A String. Then Copy All Cells Between Each Occurence


    This code will select all numeric cells in 1-st column:

    Code
    Columns(1).SpecialCells(xlCellTypeConstants,xlNumbers).Select
  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    This will take information from column A and paste in column B
    [vba]Sub x()


    Dim rngTemp As Range
    Dim rngFind As Range
    Dim rngFirst As Range
    Dim rngLast As Range
    Dim strFirstAddress As String
    Dim lngRow As Long

    lngRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    With Range("A:A")
    Set rngFind = .Find("spot.return", .Cells(lngRow, 1), LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFind Is Nothing Then
    strFirstAddress = rngFind.Address
    Set rngFirst = rngFind.Offset(1)
    Set rngTemp = rngFirst
    Set rngFind = .FindNext(rngFind)
    Do While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
    Set rngLast = rngFind.Offset(-1)
    Set rngTemp = Union(rngTemp, Range(rngFirst, rngLast))
    Set rngFirst = rngFind.Offset(1)
    Set rngFind = .FindNext(rngFind)
    Loop
    End If
    End With
    rngTemp.Copy Range("B1")
    End Sub[/vba]

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    You can use Advanced Filter to copy filtered values to another place. This macro can do it for you:

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Code
    Dim a, e, b(), i As Long, n As Long
    a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a,1), 1 To 1)
    For Each e In a
         If (UCase(e) <> "SPOT.RETURN") * (e <> "") Then
              n = n + 1 : b(n,1) = e
         End If
    Next
    Range("b1").Resize(n).Value = b
    End Sub
  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Thanks guys for the reply. I've tried all but nothing seems to work. :(


    Hi Jindon/the OKK,


    Both script you made, it just copied the whole data into column B.


    Hi Andy Pope,


    The script you made occured an error on " rngTemp.Copy Range("B1") "
    I've tried editing the script but I can't make it work.


    Regards,
    khalel04

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Quote from khalel04

    Thanks guys for the reply. I've tried all but nothing seems to work. :(


    Hi Jindon,


    script you made, it just copied the whole data into column B.
    Regards,
    khalel04


    That means your questioned text is not "Spot.Return"


    You must tell us exactly what it is.

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Quote from khalel04

    Hi Jindon/the OKK,


    Both script you made, it just copied the whole data into column B.


    Try this one:

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Hi Guys,


    "spot.return" is actually a part of a string. Sorry I forgot to tell. The whole string are these:


    spot.return_sweep (DIFFUSE) index=0 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=1 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=2 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=3 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=4 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=5 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=6 (appears 5 times)
    spot.return_sweep (DIFFUSE) index=7 (appears 5 times)



    Hi the OKK,


    Your script worked but for the first 5 occurrences only. If I'm right it's because you used Cell(1,1) as the reference point, which is index=0 only. But the concept is there. Great! I can also play with this script as well. And look if I can fix it.


    Thanks again guys.


    Regads,
    khalel04

  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    Code
    Dim a, e, b(), i As Long, n As Long
    a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a,1), 1 To 1)
    For Each e In a
         If (InStr(1,e,"spot.return",1)<>1) * (e <> "") Then
              n = n + 1 : b(n,1) = e
         End If
    Next
    Range("b1").Resize(n).Value = b
    End Sub
  • Re: Search For A String. Then Copy All Cells Between Each Occurrence


    This revision should allow finding of spot.return within a cell.
    And the copy will only occur if the range has something to be copied


    [vba]
    Sub x()

    Dim rngTemp As Range
    Dim rngFind As Range
    Dim rngFirst As Range
    Dim rngLast As Range
    Dim strFirstAddress As String
    Dim lngRow As Long

    lngRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    With Range("A:A")
    Set rngFind = .Find("spot.return", .Cells(lngRow, 1), LookIn:=xlValues, lookat:=xlPart)
    If Not rngFind Is Nothing Then
    strFirstAddress = rngFind.Address
    Set rngFirst = rngFind.Offset(1)
    Set rngTemp = rngFirst
    Set rngFind = .FindNext(rngFind)
    Do While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
    Set rngLast = rngFind.Offset(-1)
    Set rngTemp = Union(rngTemp, Range(rngFirst, rngLast))
    Set rngFirst = rngFind.Offset(1)
    Set rngFind = .FindNext(rngFind)
    Loop
    End If
    End With
    If Not rngTemp Is Nothing Then rngTemp.Copy Range("B1")
    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!