VBA.Find and adding a value

Solution 1:

Take a look at this :

Sub test_IngaB()
Dim FirstAddress As String, cF As Range, LookForString As String
LookForString = "Cat"

With ThisWorkBook.Sheets("Sheet's name").Range("F:F")
    .Cells(1,1).Activate
    'First, define properly the Find method
    Set cF = .Find(What:=LookForString, _
                After:=ActiveCell, _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)

    'If there is a result, keep looking with FindNext method
    If Not cF Is Nothing Then
        FirstAddress = cF.Address
        Do
            cF.Offset(0, 3).Value = 1
            Set cF = .FindNext(cF)
        'Look until you find again the first result
        Loop While Not cF Is Nothing And cF.Address <> FirstAddress
    End If
End With

End Sub

Solution 2:

Error message 91: `Object variable or With block variable not set.

this error means that searching is failed, there is no "Cat" in column

try this:

Sub test()
    Dim rng As Range
    Set rng = ActiveSheet.[F:F].Find("Cat")
    If Not rng Is Nothing Then
        rng.Offset(, 4).Value = 1
    Else
        MsgBox "Searching criteria does not exists in column [F]!"
    End If
End Sub

and also one comment, avoid usage of the select and selection method, this is bad practice