How to loop through rows and copy range?

Hi there,

Try the code below (assuming you are after a macro).
We are looping through all rows on the active sheet and write the address of the FIRST 'Error' for each row into the Immediate window.
With your sample data, if starting in cell A1, the result would be;
$D$1
$E$2
$C$3
$F$4
$E$5

Let us know how you go with this.

PS: please check the yellow banner at the top on how to upload a 'sanitized' sample workbook. That would make it much easier for us to see how your data is structured and test offered solutions.

Option Explicit
Sub FindError()

Dim i As Long, lRow As Long
Dim myRng As Range, cFound As Range, findValue As String

lRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

findValue = "Error" ' specify the text to search for

    For i = 1 To lRow
        Set myRng = Range("A" & i & ":Z" & i) ' specify the range to search in
        Set cFound = myRng.Find(What:=findValue, LookIn:=xlValues, LookAt:=xlWhole)

' Show result in 'Immediate' window:       
        If Not cFound Is Nothing Then Debug.Print cFound.Address
 
' Show result for each row in a Message Box:
       If Not cFound Is Nothing Then MsgBox "Row: " & i & " - First 'Error' found in cell: " & cFound.Address, , "Error found"

    Next i

End Sub

You are welcome, and thanks for getting back to us.