+ Reply to Thread
Results 1 to 3 of 3

Variable not set?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-09-2009
    Location
    Cedar Hill, Tx
    MS-Off Ver
    Excel 2003
    Posts
    200

    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
    Last edited by bstubbs; 11-05-2010 at 04:35 PM.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Variable not set?

    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.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    01-09-2009
    Location
    Cedar Hill, Tx
    MS-Off Ver
    Excel 2003
    Posts
    200

    Re: Variable not set?

    Thanks, o mighty guru.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1