Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range("lookuprange"), 1, False)


If you have a defined name (insert =name=Define) with name of oracle_no
and that is located on the sheet with the code and another defined name
(insert=name=Defined) with a name of Lookuprange found on Sheet Upload
Data, then you would use the above.

Adjust to match you actual situation.

If it isn't a defined name (insert=Name=Define), then what do you mean by
defined name - do you mean a variable named Oracle_no as in

Oracle_No = "B123"

Then you would need to declare that as a public variable at the top of a
general module (insert=Module in the VBE) above any procedures

Public Oracle_No as String
Pubic Lookuprange as String

some procedure would have to set the value of the varibles before you
clicked the button.

--
Regards,
Tom Ogilvy




"Connie" wrote in message
ups.com...
I am trying to pass a user defined name (Oracle_no) to a command
button. In the following code, I am doing a VBLOOKUP on oracle_no, but
when I debug the code, I find that oracle_no is not being passed.
Should I do this in a function instead? I guess I can't utilize
defined field names in a command button?

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
lookuprange = ("$C$2:" + rng.Address)
MsgBox "oracle no follows"
MsgBox oracle_no
Test = Application.VLookup(oracle_no, Range(lookuprange), 1, False)
MsgBox IsError(Test)
MsgBox lookuprange
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub