none
Object variable or with block variable not set - Error 91

    Question

  • I have the following code in Excel 2007 and can't for the lift of me work out why I get an error 91 on the last line of the function. As far as I can see I have Set the clientOverviewCell variable correctly. Any hints as to what I am doing wrong here?

    Public Function get_client_overview(clientID As Long) As String 
        
        Dim index As Long 
        Dim currentCell As Range 
        Dim clientOverviewCell As Range 
        
        For index = 1000 To 2 Step -1 
            Set currentCell = Sheet11.Range("A" & index) 
            If currentCell.Text = clientID Then 
                Set clientOverviewCell = Sheet11.Range("B" & index) 
                Exit For 
            Else 
                Set clientOverviewCell = Nothing 
            End If 
        Next index 
    
        get_client_overview = clientOverviewCell.Text 
    End Function 
    

    Thanks.

    Wednesday, May 22, 2013 7:45 PM

Answers

All replies

  • Try belo. Its a Range variable and its value can be accessed using  clientOverviewCell.Value or simply clientOverviewCell as value is its default property.

    Public Function get_client_overview(clientID As Long) As String
        
        Dim index As Long
        Dim currentCell As Range
        Dim clientOverviewCell As Range
        
        For index = 1000 To 2 Step -1
            Set currentCell = Sheet11.Range("A" & index)
            If currentCell.Text = clientID Then
                Set clientOverviewCell = Sheet11.Range("B" & index)
                Exit For
            Else
                Set clientOverviewCell = Nothing
            End If
        Next index
    
        get_client_overview = clientOverviewCell.Value
    End Function


    • Edited by Santosh Vi Wednesday, May 22, 2013 8:01 PM updated
    Wednesday, May 22, 2013 7:59 PM
  • Hello,

    It seems your code works fine for me. The only possible cause for error 91 I can think of is the target range contains invalid value. I suggest you checking what is in the target range("clienOverviewCell").

    For your reference: Object variable or With block variable not set (Error 91)

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 04, 2013 9:52 PM
  • Hello,

    Since you haven't responded for a few days, I temporarily mark my reply as answer. If you disagree with it, please unmark it and post your doubt. Thanks for your understanding.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 11, 2013 4:38 AM