Runtime Error 91 - when testing code in Userform

Question

Office 360, 64 bit, Excel. The subroutine, Private Sub Card_FindCardNumber_AfterUpdate() is a textbox (Card_FindCardNumber) that after I have entered in the number I am querying, the code should be looking to see if it exists.

Whilst testing the code, it doesn't matter whether it is a number that exists or doesn't exist, I get the same error message "Runtime Error 91", Object Variable or With Block variable not set.

I'm a amateur with VBA and can't work out why there is a problem with the particular line of code.

I have adapted the code from Trevor Easton,(Online PC Learning) "Excel VBA with Userform Vlookup" from YouTube and his website:

The Ws01 is actually the Code name of the spreadsheet representing cnVisitorCard_Database worksheet.

The Vlookup is referring to the named range: "LookupCardNo" which has an offset function covering data of 9 columns (from column AC (Card #) to the last column of the name range AK)

    Private Sub Card_FindCardNumber_AfterUpdate()
              'Define the Variables
              Dim ws01 As Worksheet
              Dim cnVisitorCard_Database As Worksheet
              Set ws01 = cnVisitorCard_Database

             'Check to see if value exists
             If WorksheetFunction.CountIf(ws01.Range("AC:AC"), Me.Card_FindCardNumber.Value) = 0 Then
                MsgBox "This is an incorrect ID"
                Me.Card_FindCardNumber.Value = ""
                Exit Sub
             End If

            'Lookup values based on first control
               With Me
                .Card_ExpiryDate = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 2, 0)
                .Card_Status = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 3, 0)
                .Card_ReturnDate = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 4, 0)
                .Card_Description = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 5, 0)
                .Card_TypeCode_Hidden = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 6, 0)
                .Card_ValidNo_ofDays_Hidden = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 7, 0)
                .Card_UpdatedInHW = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 8, 0)
                .Card_UpdatedInFF = Application.WorksheetFunction.VLookup(CLng(Me.Card_FindCardNumber), ws01.Range("LookupCardNo"), 9, 0)

                End With

     End Sub

I get the error 91 at the line:

 If WorksheetFunction.CountIf(ws01.Range("AC:AC"), 
Me.Card_FindCardNumber.Value) = 0 Then

I am of course either expecting it to return the values into the textboxes on the Userform if the number exists, but if it doesn't exist, then I'd expect it to slam me with an pop-up message telling me the number doesn't exist.

I'd be most grateful for any assistance :) of course I'd don't know if there will be other errors once this is cleared up.

Can you omit the line Set ws01 = cnVisitorCard_Database and check if it's running. Also drop the Dim ws01 as Worksheet. — Mikku
– Mikku, Commented Jun 17, 2019 at 3:01
As ws01 is the code name of the spreadsheet, you don't need to set it. — Mikku
– Mikku, Commented Jun 17, 2019 at 3:03
I commented out the "Set ws01 = cnVisitorCard_Database " and got the same error (01). — TheShyButterfly
– TheShyButterfly, Commented Jun 17, 2019 at 3:11
I commented out the "Dim ws01 as Worksheet", and of course I got the error that states that the variable ws01 has not been defined. — TheShyButterfly
– TheShyButterfly, Commented Jun 17, 2019 at 3:14
I have the Option Explicit at the top of the code window, so the worksheet/s would have to be defined … I haven't had a problem with the that part of the code in other projects that I have made — TheShyButterfly
– TheShyButterfly, Commented Jun 17, 2019 at 3:16

Tim Williams · Accepted Answer · 2019-06-17 05:45:51Z

If you already have a sheet with codename cnVisitorCard_Database then this variable declaration will "hide" that sheet:

Dim cnVisitorCard_Database As Worksheet

so your local variable has a value of Nothing. So when you do this:

Set ws01 = cnVisitorCard_Database

you are really writing

Set ws01 = Nothing

This doesn't raise an error, but makes your Countif call on the next line fail.

This should work (and the code is a bit simpler/shorter):

Private Sub Card_FindCardNumber_AfterUpdate()

    Dim rngSrch As Range, m 'as variant

    Set rngSearch = cnVisitorCard_Database.Range("LookupCardNo")

    m = Application.Match(CLng(Me.Card_FindCardNumber), rngSearch.Columns(1), 0)

    If IsError(m) Then
        'if no match then m is an error value
        MsgBox "This is an incorrect ID"
        Me.Card_FindCardNumber.Value = ""
    Else
        'if found match then m is the matching row number in rngSearch
        With rngSearch.Rows(m)
            Me.Card_ExpiryDate = .Cells(2).Value
            '...etc
            Me.Card_UpdatedInFF = .Cells(9).Value
        End With
    End If
End Sub

Collectives™ on Stack Overflow

Runtime Error 91 - when testing code in Userform

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related