Results 1 to 3 of 3

Variable not set?

Threaded 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.

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