VBA Userform: Check sheet for existing data

[Clova13]

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:

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

 

[mikerickson]

You code misuses Nothing, try this formulation

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?

 

[Clova13]

Hey, thanks for your help again

BSCQC is the name of the actual worksheet.

Tried your suggestion but still getting the same error

 

[mikerickson]

The sytanx for refereing to the sheet would be

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

 

[Clova13]

Okay, I'm now getting an error 1004 - it doesn't like the vlookup function

 

[mikerickson]

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.

 

[Clova13]

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?

 

[mikerickson]

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.

 

[Clova13]

Great. Thanks for your help.

Merry Christmas!