Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006

    Error 91 in Excel 2004.

    Hi Folks,

    I have spent almost the whole day trying to figure this problem out. in the Macro code listed below. If I call the function from a testing subroutine, it works fine. But is I use the function in an Excel spreadsheet cell, I get the "Object variable or With block variable not set (Error 91) ..." .(Only if I trap the error)

    Please HELP!!

    *********** Code Below *****************

    Sub Test_GetTier()
    irow = 4482
    SC = Worksheets("Inventory Table").Cells(irow, 2)
    band = Worksheets("Inventory Table").Cells(irow, 3)
    UpBm = Worksheets("Inventory Table").Cells(irow, 4)
    DnBm = Worksheets("Inventory Table").Cells(irow, 5)
    retVal = getTier(SC, band, UpBm, DnBm) ' This works correctly.

    End Sub
    Function getTier(SC, band, UpBm, DnBm) As Integer

    Dim sht As String
    Dim numericSC As Variant
    Dim upbeamVal As String, dnbeamVal As String
    Dim myTable As Range
    Dim objfound As Object

    numericSC = CSng(SC)

    sht = "TierLevel"
    Set objfound = Nothing
    gotit = -99

    If band = "C" Then
    Set myTable = Worksheets(sht).Range("C_Band_Tiers")
    Set myTable = Worksheets(sht).Range("Ku_Band_Tiers")
    End If

    'objfound = myTable.Cells.Find(what:=numericSC)

    With myTable
    Set objfound = .Find(numericSC, LookIn:=xlValues, searchorder:=xlByColumns)
    If Not objfound Is Nothing Then
    firstAddress = objfound.Address
    upbeamVal = objfound.Offset(0, 1).Value
    dnbeamVal = objfound.Offset(0, 2).Value
    If upbeamVal = UpBm Or upbeamVal = "Any" Then
    If InStr(dnbeamVal, DnBm) > 0 Then
    gotit = objfound.Offset(0, 4).Value
    getTier = gotit
    Exit Function
    End If
    End If
    On Error GoTo XXX
    objfound.Value = numericSC
    Set objfound = .FindNext(objfound) ' It blow up here if used as a function in the Cell.
    Loop While Not objfound Is Nothing And objfound.Address <> firstAddress
    End If
    End With
    getTier = gotit
    Exit Function

    MsgBox (Err.Number & " .. " & Err.Description)
    getTier = "Err:= & Err.num"

    End Function

  2. #2
    Join Date
    Aug 2006

    Error 91 continued by Srikar

    By the way, I used the code below as a guide. It was the example from MS Excel help files.


    With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    c.Value = 5
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With

Posting Permissions

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