VLOOKUP Limitations

[chris_manning]

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.

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[chris_manning]

Jeez-
I knew it would be some stupid detail that I forgot. I had listed the 'FALSE' argument in the formulas that were in the cells, so that is why it worked for the values, but not for the hyperlinks. Thanks a ton, that seems to have cleared up the problems in total.

Chris

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>

[bpeltzer]

For starters, I'd try adding a fourth argument to your lookup functions:
,FALSE (ex: vlookup(a2,Sheet2!b:c,2,false) ).
Without that, you're getting a 'range' lookup instead of requiring an exact
match. That would result in #N/A being returned if your key value is 'less
than' the first entry in your table. If your table isn't sorted, it would
also explain incorrect values being returned.

"chris_manning" wrote:

>
> 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=393454
>
>