Object variable or With block variable not set - Userform Data Validation

Question

I am getting the

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

on this particular bit of code, I can't work out what is wrong with it ...

'Booking Number Validation
With Sheets("New Enquiries")
Dim r As Excel.Range
Set r = .Range("A:A").Find(What:=BookingNumberTextBox.Text, LookAt:=xlWhole, MatchCase:=False)

    If r = BookingNumberTextBox.Text Then
        MsgBox ("Booking Number already exists.")
        Call UserForm_Initialize
    Else
        MsgBox ("Enquiry has been added.")
    End If

End With

I am getting the error on line If r = BookingNumberTextBox.Text Then

The point of this is to look when adding data via the userform,

if the booking number already exists, tell the user then initialize the userform,
if it doesn't exist, add the data and confirm entry.

EDIT: Based on YowE3K's answer, I amended his code and came up with the following;

'Booking Number Validation
With Sheets("New Enquiries")
    Dim r As Excel.Range
    Set r = .Range("A:A").Find(What:=BookingNumberTextBox.Text, LookAt:=xlWhole, MatchCase:=False)

    If r Is Nothing Then
        MsgBox "Enquiry has been added."
    Else
        If r.Value = BookingNumberTextBox.Text Then
            MsgBox "Booking Number already exists."
            Call UserForm_Initialize
        End If
    End If
End With

What does debug.print BookingNumberTextBox.Text print out? — user7857211
– user7857211, Commented May 5, 2017 at 9:30

YowE3K · Accepted Answer · 2017-05-05 10:36:30Z

3

You aren't checking to see whether the value already exists before you try to use the range:

'Booking Number Validation
With Sheets("New Enquiries")
    Dim r As Excel.Range
    Set r = .Range("A:A").Find(What:=BookingNumberTextBox.Text, LookAt:=xlWhole, MatchCase:=False)

    If r Is Nothing Then
        'Find was not successful - do whatever you want in that situation
        '...
        '...
    Else
        'Find was successful
        If r.Value = BookingNumberTextBox.Text Then
            MsgBox "Booking Number already exists."
            Call UserForm_Initialize
        Else
            'You shouldn't ever reach this spot because you were searching
            'for BookingNumberTextBox.Text, so r.Value should be equal to it
            MsgBox "Enquiry has been added."
        End If
    End If

End With

Based on the OP's revised code, the final solution can be simplified to:

'Booking Number Validation
With Sheets("New Enquiries")
    Dim r As Excel.Range
    Set r = .Range("A:A").Find(What:=BookingNumberTextBox.Text, LookAt:=xlWhole, MatchCase:=False)

    If r Is Nothing Then
        MsgBox "Enquiry has been added."
    Else
        MsgBox "Booking Number already exists."
        Call UserForm_Initialize
    End If
End With

edited May 5, 2017 at 10:36

answered May 5, 2017 at 9:29

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panacea06 Over a year ago

Thank you YowE3K, with a bit of playing around with your code, I got what I wanted which I have updated my original post with!

YowE3K Over a year ago

@Panacea06 - I did wonder whether that was what you were after, but I couldn't see what "Enquiry has been added" meant when nothing was being processed. (But that could be simply due to you having supplied the MCVE that we always like in questions! :) )

YowE3K Over a year ago

@Panacea06 - I updated the answer to show a simplification within the If - there is no need to test whether the value in r is equal to BookingNumberTextBox.Text - it has to be equal because r was set to be a cell that had a value that was equal.

Collectives™ on Stack Overflow

Object variable or With block variable not set - Userform Data Validation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related