Thread: Variable not set?

I've got the following code:

Function convertDriverName(driverName)
    Dim c As Range
    With Worksheets("lookup data").Range("a1:a500")
        Set c = .Find(driverName, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            driverName = c.Offset(0, 2)
        End If
    End With
    convertDriverName = driverName
End Function

Sub findDrivers()
    Dim driverName, firstAddress, searchStr As String
    Dim r As Range
    Range("k2:k10000").Clear
    searchStr = "Employee:"
    Application.Calculation = xlCalculationManual
    With Worksheets("raw data - after").Range("d1:d10000")
        Set r = .Find(searchStr)
        If Not r Is Nothing Then
            firstAddress = r.Address
            Do
                r.Offset(-2, 7) = r.Offset(0, 1)
                Set r = .FindNext(r)
            Loop While Not r Is Nothing And r.Address <> firstAddress
        End If
    End With
    
    searchStr = "Driver:"
    With Worksheets("raw data - after").Range("d1:d10000")
        Set r = .Find(searchStr)
        If Not r Is Nothing Then
            firstAddress = r.Address
            Do
                driverName = r.Offset(0, 1)
                driverName = convertDriverName(driverName) ' <== no problem if commented out
                r.Offset(-2, 7) = driverName
                Set r = .FindNext(r)
            Loop While Not r Is Nothing And r.Address <> firstAddress
        End If
    End With
    Application.Calculation = xlCalculationAutomatic
    Range("a1").Select
    MsgBox ("Process complete...")
End Sub

Everything works fine if I have the call to the convertDriverName function commented out (see <== line, above). However, if the line is not commented out, I got an "Object variable or With block variable not set" error. Maybe I'm too close to the error, but I can't see what the problem may be. Can anyone help?

TIA

Bob

Your problem is that the Find in the called function overrides all the settings of the first find, and hence the FindNext fails. Instead of using FindNext, you will have to repeat the Find operation and specify the same parameters as you did originally.