I have some problems defining that check. It's only a part of whole code with Do Untilloop.

If any row in my sheet matches searched value define FindRow as "1".

If matching row is found then FindRow = that row number.

With ThisWorkbook.Sheets("TKSLIST").Range("A:A")
    FindRow = 1
    strTKSname = ws.Cells(i   1, 11).Text
    
    If .Find(What:=strTKSname, LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False).Row Is Nothing Then
    FindRow = 1
    Else
    FindRow = .Find(What:=strTKSname, LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False).Row
    End If
End With

I'm missing there something. Everytime when my searched value is not found I'm getting errors.

CodePudding user response：

1. Declare your variables properly

2. Get the range wher something was found as FoundAt and compare that against Nothing

3. Remove else part because FindRow = 1 was already set as initial value.

With ThisWorkbook.Sheets("TKSLIST").Range("A:A")
    Dim FindRow As Long
    FindRow = 1  ' this needs to be inside the loop (and you can omit the else part below)

    Dim strTKSname As String
    strTKSname = ws.Cells(i   1, 11).Text
    
    Dim FoundAt As Range
    Set FoundAt = .Find(What:=strTKSname, LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False)

    If Not FoundAt Is Nothing Then
        FindRow = FoundAt.Row
    End If 'no else needed because initialized as `FindRow = 1`
End With

CodePudding user response：

Using the Find Method in a Loop

Sub Whatever()

    ' somewhere before the loop
    Dim fCell As Range
    Dim FindRow As Long

                    With ThisWorkbook.Sheets("TKSLIST").Range("A:A")
                        strTKSname = ws.Cells(i   1, 11).Value
                        ' Just a reminder:
                        ' Without the 'After' the search starts with `A2`.
                        ' If that is ok with you, you don't need it.
                        Set fCell = .Find(What:=strTKSname, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, LookAt:=xlPart)
                        If fCell Is Nothing Then
                            FindRow = 1
                        Else
                            FindRow = fCell.Row
                        End If
                    End With

End Sub