Need help with cells.find and do if looping

Hello wellsw,

Try this...

    Dim C As Range
    Dim FirstAddress As String
    Dim LR As Long
    Dim Rng As Range
    
        Set Rng = Range("C2:C15")
        
            Set C = Rng.Find("#N/A", , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
            
            If Not C Is Nothing Then
                FirstAddress = C.Address
                LR = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
                
                Do
                    LR = LR + 1
                    Cells(C.Row, 1).Resize(1, 4).Copy Sheet2.Cells(LR, "A")
                    Set C = Rng.FindNext(C)
                    If C Is Nothing Then Exit Do
                Loop Until C.Address = FirstAddress
            End If

The reason that the Find is looking in all cells, and not just column C, is that is what is specified. The code specifies

Cells.Find

and Cells is a reference to the entire worksheet. You have used

With Range("C2:C15")

but then the code never actually takes advantage of that With. To do that you want something like this:

With Range("C2:C15")

Set C = .Find("#N/A")

The .Find means "Do a find on the range I have specified in my With statement."

However, that doesn't solve your problem I just am trying to do a little education there. The bigger problem is that a Find can only find a string, it cannot find cells that fail to match a string. That would have to be done as an explicit loop. I have suggested one solution here. You will also note that I declare all variables, which I highly recommend (along with Option Explicit, which is automatically provided in new modules if you set Tools, Options, Require Variable Declaration).

I have tested this code on your file and I believe it does what you want:

Option Explicit

Sub Test()

   Dim C As Range
   Dim LR As Long
      
   For Each C In Range("C2:C15")
      If C.Text <> "#N/A" Then
         LR = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
         Union(Cells(C.Row, 1), Cells(C.Row, 2), Cells(C.Row, 3), Cells(C.Row, 4)).Copy Sheet2.Cells(LR + 1, "A")
      End If
   Next C

End Sub