Vlookup?
 

Firstly, Hello all,

I have the following code which is activated on a command button to go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right of
it and write that into another text box (txtloanmemName1). The data in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?


Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit using XP Pro and Office 2000.

Any pointers would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Vlookup?
 

Hi
if database is a defined name try
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
Range("database"), 2,0)


--
Regards
Frank Kabel
Frankfurt, Germany


Firstly, Hello all,

I have the following code which is activated on a command button to
go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right
of
it and write that into another text box (txtloanmemName1). The data
in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?


Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value =
WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit using XP Pro and Office 2000.

Any pointers would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Vlookup?
 

If you use application.vlookup, you can return an error value.

If you use worksheetfunction.vlookup, any error needs to be trapped by your
code:

Private Sub Cmdbtnpop1_Click()
dim res as variant
res = application.vlookup(txtloanmemno1.value,database,2 )

if iserror(res) then
txtloanmemname1.value = "missing"
else
txtloanMemName1.Value = res
end if
End Sub

Private Sub Cmdbtnpop1_Click()
dim res as variant
on error resume next
res = WorksheetFunction.VLookup(txtloanMemNo1.Value,data base, 2)
if err.number < 0 then
textloanmemname1.value = "missing"
else
textloandmemname1.value = res
end if
End Sub


I normally have my vlookup look for an exact match when working with text:

....vlookup(txtloanmemno1.value,database,2,false)


"Seedubs <" wrote:

Firstly, Hello all,

I have the following code which is activated on a command button to go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right of
it and write that into another text box (txtloanmemName1). The data in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?

Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit using XP Pro and Office 2000.

Any pointers would be appreciated.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Vlookup?
 

Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
err.Clear
TxtloanMemName1.Value = res
End if
End Sub


If the value being searched for is not found, then the vlookup raises a
trappable error. So you have to account for this. Also, not sure what
"database" means in your procedure, but a valid range reference must be
used. If you have a defined name "database", you could use
Range("Database") but it wasn't clear that is the case.

--
Regards,
Tom Ogilvy



"Seedubs " wrote in message
...
Firstly, Hello all,

I have the following code which is activated on a command button to go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right of
it and write that into another text box (txtloanmemName1). The data in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?


Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit using XP Pro and Office 2000.

Any pointers would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Vlookup?
 

Guess I don't need to clear the error if it didn't occur <g

Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
TxtloanMemName1.Value = res
Else
err.clear
TxtloanMemName1.Value "Not found"
End if
End Sub

--
regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Private Sub Cmdbtnpop1_Click()
on Error Resume Next
res = WorksheetFunction. _
VLookup(txtloanMemNo1.Value,Worksheets("Sheet2"). _
Range("A1:B500"),2)
if err.number = 0 then
err.Clear
TxtloanMemName1.Value = res
End if
End Sub


If the value being searched for is not found, then the vlookup raises a
trappable error. So you have to account for this. Also, not sure what
"database" means in your procedure, but a valid range reference must be
used. If you have a defined name "database", you could use
Range("Database") but it wasn't clear that is the case.

--
Regards,
Tom Ogilvy



"Seedubs " wrote in message
...
Firstly, Hello all,

I have the following code which is activated on a command button to go
to another worksheet and look for a value (in the text box
txtloanMemNo1) and then return the value in the column to the right of
it and write that into another text box (txtloanmemName1). The data in
the other worksheet is set as a database, but I have two other sheets
with databases in them too? This code is erroring with:

Unable to get the VLookup property of the Worksheetfunction class.

Any ideas - is it looking in the wrong place - do I have to name the
database or sheet?


Private Sub Cmdbtnpop1_Click()
TxtloanMemName1.Value = WorksheetFunction.VLookup(txtloanMemNo1.Value,
database, 2)
End Sub

This code is part of a user form.

<edit using XP Pro and Office 2000.

Any pointers would be appreciated.


---
Message posted from http://www.ExcelForum.com/