Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    run-time error: 91 in a VBA

    My Excel knowledge is not that great. I am trying to de-bug something that an old employee created. In my worksheet I have a button to "Hide to Today on All". Once it reaches the end of the tabs I get the run-time error: 91. When I click de-bug the below section is highlighted with the arrow pointing at the last row.

    Cells.Find(What:=Monserial, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    Is something obviously missing? I have nothing to compare this to to even find an error.

    Thank you!
    Whitney

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That error means that whatever value is in Monserial is not found in the search range.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I do not know what Monserial is.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's a variable in your code. Can't say any more than that from the little you have posted, I'm afraid.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Jan 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Found the variable. Here is the entire section in question:

    Sub Hide2ThisWeek()
    UnhideAll
    FirstRow = 6
    Dim CurDate As String
    Dim CurDayWeek As Integer
    Dim CurDay As String
    CurDate = Date
    'CurDayNumber = Day(Date) Mod 7
    CurDayWeek = Weekday(Date) - 1
    CurDay = WeekdayName(CurDayWeek, True, vbMonday)


    D1 = DatePart("d", CurDate)
    D2 = DatePart("m", CurDate)
    D3 = DatePart("yyyy", CurDate)
    CurSerial = DateSerial(D3, D2, D1)
    Monserial = DateSerial(D3, D2, D1 - CurDayWeek + 1)


    Cells.Find(What:=Monserial, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    myRow = ActiveCell.Row
    Rows(FirstRow & ":" & myRow - 1).Select
    Selection.EntireRow.Hidden = True
    Range("A1").Select
    End Sub

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Where is Monserial declared (there should be a Dim statement)?

    When using Find you should always check if the search value is found before trying to activate the found cell:

    Code:
        Dim rgFound               As Range
        Set rgFound = Cells.Find(What:=monserial, After:=ActiveCell, LookIn:=xlFormulas, _
                                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=False, SearchFormat:=False)
        If rgFound Is Nothing Then
            MsgBox "Date: " & monserial & " not found"
        Else
            myRow = rgFound.Row
            Rows(FirstRow & ":" & myRow - 1).EntireRow.Hidden = True
            Range("A1").Select
        End If
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

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