Pass Defined Name to a Command Button

[Connie]

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

 

[Tom Ogilvy]

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.

 

[Connie]

I got it to work, however I have a couple of questions/comments:

In answer to your question, Oracle_No is defined (insert->name->define)
on the sheet with the code. LookUpRange is defined in the code:

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)
Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range(lookuprange), 1, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub

When I use the debugger to trace oracle_no (or even print a message
box), oracle_no is blank. Why is it not returning the oracle_no?

Is there an easy way to delete all rows in LookUpRange containing the
oracle_no?

Thanks, Tom.

Connie

 

[Connie]

I got it to work, however I have a couple of questions/comments:

In answer to your question, Oracle_No is defined (insert->name->define)
on the sheet with the code. LookUpRange is defined in the code:

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)
Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range(lookuprange), 1, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub

When I use the debugger to trace oracle_no (or even print a message
box), oracle_no is blank. Why is it not returning the oracle_no?

Is there an easy way to delete all rows in LookUpRange containing the
oracle_no?

Thanks, Tom.

Connie