Vlookup
 

Hi all-

I'm having a problem with the VLookup function. I have an excel fil
which automatically fills out several fields in a row when a certai
cell from that row is filled. The 'key' off of which the other field
key is filled via a drop-down data validation list. The 'subjugate
cells then are filled with values using the VLookup function. All o
this works to this point. However, I also use the VLookup function i
the coding for the sheet in order to automatically hyperlink some othe
relevant data. Some of the choices from the dropdown menu give me a
error, specifically:

Run-time erro '1004':
Unable to get the VLookup property of the WorksheetFunction class

What is perplexing is that only certain choices result in this error
whilst others work exactly in the method they are supposed to, th
VLOOKUP Formulas in the cells always 'grab' the correct tex
information. Other choices from the cell have the right text displayed
but an incorrect hyperlink. If it makes any difference, the locations o
the data from the list are formatted as 'General' text, and while som
of the choices are only numbers, some of them are numbers AND letters
and it seems to be predominantly the selections that contain number
and letters that have error issues. (For example, a selection of '115
from the list results in a correct listing of text display in th
'subjugate' cells as well as correct hyperlinks, whereas a selection o
'1035Zn' results in a 1004 error) Is this an artifact of the inheran
nature of the VLOOKUP function itself, or is it something that I'
doing wrong?

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then
Cells(3, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=""
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column)
Range("A131:C200"), 3)
Cells(2, Target.Column).Select
Cells(6, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=""
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column)
Range("A131:G200"), 7)
Cells(2, Target.Column).Select
Cells(4, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column)
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
Cells(5, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column)
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
End If
End Sub

Thanks in advance for the help

--
chris_mannin
-----------------------------------------------------------------------
chris_manning's Profile: http://www.excelforum.com/member.php...fo&userid=2398
View this thread: http://www.excelforum.com/showthread.php?threadid=39346

Vlookup
 

Something to try:

Use the 4th argument of the vlookup function - set it to False for an exact
match.

--
Regards,
Tom Ogilvy


"chris_manning"
wrote in message
news:chris_manning.1tbaec_1123279529.7531@excelfor um-nospam.com...

Hi all-

I'm having a problem with the VLookup function. I have an excel file
which automatically fills out several fields in a row when a certain
cell from that row is filled. The 'key' off of which the other fields
key is filled via a drop-down data validation list. The 'subjugate'
cells then are filled with values using the VLookup function. All of
this works to this point. However, I also use the VLookup function in
the coding for the sheet in order to automatically hyperlink some other
relevant data. Some of the choices from the dropdown menu give me an
error, specifically:

Run-time erro '1004':
Unable to get the VLookup property of the WorksheetFunction class

What is perplexing is that only certain choices result in this error,
whilst others work exactly in the method they are supposed to, the
VLOOKUP Formulas in the cells always 'grab' the correct text
information. Other choices from the cell have the right text displayed,
but an incorrect hyperlink. If it makes any difference, the locations of
the data from the list are formatted as 'General' text, and while some
of the choices are only numbers, some of them are numbers AND letters,
and it seems to be predominantly the selections that contain numbers
and letters that have error issues. (For example, a selection of '115'
from the list results in a correct listing of text display in the
'subjugate' cells as well as correct hyperlinks, whereas a selection of
'1035Zn' results in a 1004 error) Is this an artifact of the inherant
nature of the VLOOKUP function itself, or is it something that I'm
doing wrong?

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then
Cells(3, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:C200"), 3)
Cells(2, Target.Column).Select
Cells(6, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:G200"), 7)
Cells(2, Target.Column).Select
Cells(4, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
Cells(5, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
End If
End Sub

Thanks in advance for the help.


--
chris_manning
------------------------------------------------------------------------
chris_manning's Profile:
http://www.excelforum.com/member.php...o&userid=23986
View this thread: http://www.excelforum.com/showthread...hreadid=393469