VBA FindNext not finding next

[Alroj]

Hello,

I am having trouble with the script below and I am not sure what it is not correct.
The script looks for the word "EQ" in column J and then selects the 3 cells in front of the text (cells in column H,I,J) to cut and then paste them in column G. The script is only doing the first one but then I get the Run-time error '1004': Unable to get the FindNext property of the Range class

Could someone please assist shedding light on this issue

Cheers

Sub FindAndCut()

   Dim C As Range
 

   With ActiveSheet.Range("j2:j20000")
        Set C = .Find("EQ", LookIn:=xlValues)
        
        If Not C Is Nothing Then
            firstAddress = C.Address
            Do
           Range(C, C.Offset(0, -2)).Select
             Selection.Cut Selection.Offset(0, -1)
             
           Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
        End If
    End With

End Sub

 

[Alex Blakenburg]

You are moving the cell you have found by the cut-paste action. This is:
• upsetting your find next since the cell is no longer in column J
• means you are never to return to the firstAddress, for the same reason.
see if this works for you.

Sub FindAndCut()

    Dim C As Range
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Range("J" & Rows.Count).End(xlUp).Row
    Set rng = ws.Range("J2:J" & lastRow)
           
    Set C = rng.Find("EQ", LookIn:=xlValues)
    
    If Not C Is Nothing Then
        Do
            ws.Cells(C.Row, C.Column - 3).Resize(, 3).Value = ws.Cells(C.Row, C.Column - 2).Resize(, 3).Value
            C.ClearContents
         
            Set C = rng.FindNext(C)
        Loop Until C Is Nothing
    End If

End Sub

 

[Alroj]

Hi Alex Blakenburg, this worked very well. Thank you for sharing your wisdom, I've learnt something new today

Cheers!

​

 

[Alex Blakenburg]

You're welcome. Glad I could help.