Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Charlotte, NC

    Question Unanswered: Problem setting range object


    I have a problem setting a range object to be returned from one of my functions. I use a set keyword to set the return value (which is accepted), but I get the error:

    "Object variable or With block variable not set (Error 91)"

    when I run the code.

    I call the function with:

    rTarget = FindVal(wstTarget.Name, sDate, sTOB)
    and the function itself is:

    Function FindVal(sSheet As String, sDate As String, sTOB As String) As Range
        ' Return cell range for target value
        Dim iRow As Integer
        Dim iCol As Integer
        iRow = 2
        iCol = 2
        Dim wstSheet As Worksheet
        Set wstSheet = ThisWorkbook.Worksheets(sSheet)
        Do Until wstSheet.Cells(iRow, 1).Value = sTOB
            iRow = iRow + 1
        Do Until wstSheet.Cells(1, iCol).Value = sDate
            iCol = iCol + 1
            If wstSheet.Cells(1, iCol).Value = "" Then
                Exit Do
            End If
        Set FindVal = wstSheet.Cells(iRow, iCol)
    End Function
    Does anyone have any idea what is wrong? When I debug the code, I get the error on the End Function line, but it steps through all the rest of the code in the fuction OK.

    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Feb 2004
    Quote Originally Posted by robojam
    Set FindVal = wstSheet.Cells(iRow, iCol)
    End Function

    Does anyone have any idea what is wrong?
    I have never seen the 'Set' statement used in returning a function value, at least not like this. Doesn't mean its not possible or correct, Didn't test it.

    You might try first setting an object variable then assigning the Var to your function return value.

    Set nVal = wstSheet.Cells(iRow, iCol)
    FindVal = nVal


  3. #3
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    I thing this is the problem, you need to also 'Set' the rTarget also

    Set rTarget = FindVal(wstTarget.Name, sDate, sTOB)

    The Set Keyword assigns an object reference to a variable, therefore to change the assigment of any object (rTarget is an object variable and FindVal returns an object) you need the Set ketword.



Posting Permissions

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