none
Restrict Search to current column RRS feed

  • Question

  • I wrote the following code to find a cell value, select the rest of the column beneath that cell and cut and paste it to right of the current column. I need to restrict the search to the current column so the macro stops when it finds the last instance of the search string. How do I do that?

    Sub FindAndReplace()

    Do
    ActiveCell.Select
    Set cell = Cells.Find(What:="Datasheet*Added*", LookIn:=xlValues, After:=ActiveCell, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    cell.Activate
    If cell Is Nothing Then Exit Do
    Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 1)).Select
    Selection.Cut
    ActiveCell.Offset(0, 2).Select
    ActiveCell.EntireColumn.Range("A1").Select
    ActiveSheet.Paste
    Loop



    End Sub

    Saturday, June 8, 2013 3:51 PM

Answers

  • @LEScott
    Your code finds the first instance.

    @DFarth
    To find the last instance without Find method:

    Sub fMain()
        Dim lngRow As Long
        Dim lngLast As Long
        Dim strCol As String
        
        strCol = "A" '<- change to suit
        lngLast = Cells(Rows.Count, strCol).End(xlUp).Row
        
        For lngRow = lngLast To 1 Step -1
            If Cells(lngRow, strCol) Like "*Datasheet*Added*" Then
                Exit For
            End If
        Next lngRow
        If lngRow = 0 Or lngRow = lngLast Then Exit Sub
        
        lngRow = lngRow + 1
        Cells(lngRow, strCol).Resize(lngLast - lngRow + 1).Cut _
         Destination:=Cells(lngRow, strCol).Offset(, 1)
    End Sub


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by DFarth Sunday, June 9, 2013 2:02 PM
    Saturday, June 8, 2013 8:36 PM

All replies

  • Try

    Set cell = columns(activecell.column).Find(What:="Datasheet*Added*", LookIn:=xlValues, After:=ActiveCell


    Leo Scott

    • Proposed as answer by LEScott Saturday, June 8, 2013 5:49 PM
    Saturday, June 8, 2013 5:49 PM
  • @LEScott
    Your code finds the first instance.

    @DFarth
    To find the last instance without Find method:

    Sub fMain()
        Dim lngRow As Long
        Dim lngLast As Long
        Dim strCol As String
        
        strCol = "A" '<- change to suit
        lngLast = Cells(Rows.Count, strCol).End(xlUp).Row
        
        For lngRow = lngLast To 1 Step -1
            If Cells(lngRow, strCol) Like "*Datasheet*Added*" Then
                Exit For
            End If
        Next lngRow
        If lngRow = 0 Or lngRow = lngLast Then Exit Sub
        
        lngRow = lngRow + 1
        Cells(lngRow, strCol).Resize(lngLast - lngRow + 1).Cut _
         Destination:=Cells(lngRow, strCol).Offset(, 1)
    End Sub


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by DFarth Sunday, June 9, 2013 2:02 PM
    Saturday, June 8, 2013 8:36 PM
  • Thanks
    Sunday, June 9, 2013 2:03 PM