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
Bookmarks