none
Run-time error 91: Object variable or With block variable not set

    Question

  • Ok, I know this is a common problem and there are many posts, but none of the posts that I've read seem to have an answer that helps me.

    So sorry for posting another Error 91 question...

    I've created a sub that gets variables passed in ByVal.

    Inside the sub, I've declared some objects and tried to set them.  But it still doesn't seem to work...

    Here's how I'm declaring and setting my objects.

    Dim intRow As Integer Dim objWB As Workbook Set objWB a = Application.ThisWorkbook Dim objWS As Worksheet Set objWS = objWB.Worksheets(strWS)

    intRow = objWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    strWS is the name of the variable that is passed in ByVal

    The last line generates the error. What am I doing wrong? Thanks in advance for your help.



    • Edited by _W_ Thursday, January 31, 2013 6:39 AM
    Thursday, January 31, 2013 3:27 AM

Answers

  • If the worksheet specified by strWS is entirely blank, objWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas) will be Nothing, and trying to get its Row property will cause error 91. Try

    Dim objRng As Range
    Set objRng = objWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas)
    If objRng Is Nothing Then
        ' Sheet is empty
        intRow = 1
    Else
        intRow = objRng.Row
    End If


    Regards, Hans Vogelaar

    Thursday, January 31, 2013 7:08 AM

All replies

  • If the worksheet specified by strWS is entirely blank, objWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas) will be Nothing, and trying to get its Row property will cause error 91. Try

    Dim objRng As Range
    Set objRng = objWS.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas)
    If objRng Is Nothing Then
        ' Sheet is empty
        intRow = 1
    Else
        intRow = objRng.Row
    End If


    Regards, Hans Vogelaar

    Thursday, January 31, 2013 7:08 AM
  • Thanks Hans! This definitely answers the first Error 91 I encountered.  I've got a few more in my code.  I'm guessing that the Find result is "Nothing" too.  So I'll continue debugging and I might post some more later.  Thanks again!
    Thursday, January 31, 2013 9:29 AM