VBA Userform: Check sheet for existing data

Clova13

New Member
Joined
Dec 2, 2018
Messages
10
Hi all :)

I can't get my VLookup function to work how I need it to.

I have two worksheets (BSCQC and TMQC) and they each have a table (Table1 and Table 2, respectively). When someone entered a Call ID into the Call ID textbox (named CallID1) on the form, I need it to check that call ID isn't already on one of my two tables. I've got the following code, at the moment:

Code:
If Application.WorksheetFunction.VLookup(Me.CallID1, BSCQC.Range("Table1"), 3, FALSE) is Nothing Then
MsgBox "Call ID has already been used"
Me.CallID1.Value = ""
End If

If Appliction.WorksheetFunction.VLookup(Me.CallID1, TMQC.Range("Table2"), 3, FALSE) is Nothing Then
MsgBox "Call ID has already been used"
Me.CallID1.Value = ""
End If

I just keep getting an error: 424, Object required. Any ideas?

Thanks!

Rachel
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You code misuses Nothing, try this formulation

Code:
If IsError(Application.WorksheetFunction.VLookup(Me.CallID1, BSCQC.Range("Table1"), 3, FALSE)) Then

Also, is the worksheet name "BSCQC" or is that the name of a variable that has been assigned the value of the worksheet object?
 
Upvote 0
Hey, thanks for your help again :)

BSCQC is the name of the actual worksheet.

Tried your suggestion but still getting the same error :(
 
Upvote 0
The sytanx for refereing to the sheet would be

Code:
If IsError(Application.WorksheetFunction.VLookup(Me.CallID1, Worksheets("BSCQC").Range("Table1"), 3, FALSE)) Then
 
Upvote 0
How many columns are in the table?
If you are only testing for existance of the ID, why is the VLOOKUP returning a value from the third column.
 
Upvote 0
Each table has 26 columns. 4 of them are free-type entries so the user can pop in what they want. I want to be safe - just in case someone happens to type these specific numbers in that box. Make sense?
 
Upvote 0
As deployed, it doesn't matter if the ID is in some other column(s).
As is, if the ID is not in the first column, the MsgBox appears.
If you want to test the whole table, you should use CountIf not VLOOKUP.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top