If no matching row for a searched value found

user_9300766 0
Viewed 41

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.

vba excel
2 Answers

  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
user_32196130

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
user_98140690
Related