When I call Application.VLookup() with the col_index_num parameter = 1 and
range_lookup=False, the function returns strange numeric values -- they look
like something close to a row index for the matching value, but it's
definitely NOT returning the matching string values in Column 1 of the data
table range being searched. After the call IsError(retVal) is False, so the
function thinks it has found an exact match. The exact same call with
col_index_num set to some value 1 works fine and returns the expected data.
Note also that using WorksheetFunction.VLookup() seems to make no
difference. Is this a known bug? Does anyone know how to fix it?

It isn't a known bug, because it works fine for me. It should return the
same value you were searching for.

Dim retVal as Variant
retVal = Applicaton.Vlookup("ABC",Range("A1:Z26"),1,False)
if not iserror(retVal) then
msgbox "You looked for ABC and found: " & retVal
else
msgbox "ABC not found"
End if


--
Regards,
Tom Ogilvy



"JSzymanski" wrote in message
...
When I call Application.VLookup() with the col_index_num parameter = 1 and
range_lookup=False, the function returns strange numeric values -- they
look
like something close to a row index for the matching value, but it's
definitely NOT returning the matching string values in Column 1 of the
data
table range being searched. After the call IsError(retVal) is False, so
the
function thinks it has found an exact match. The exact same call with
col_index_num set to some value 1 works fine and returns the expected
data.
Note also that using WorksheetFunction.VLookup() seems to make no
difference. Is this a known bug? Does anyone know how to fix it?

Post the exact code you are using.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JSzymanski" wrote in
message
...
When I call Application.VLookup() with the col_index_num
parameter = 1 and
range_lookup=False, the function returns strange numeric
values -- they look
like something close to a row index for the matching value, but
it's
definitely NOT returning the matching string values in Column 1
of the data
table range being searched. After the call IsError(retVal) is
False, so the
function thinks it has found an exact match. The exact same
call with
col_index_num set to some value 1 works fine and returns the
expected data.
Note also that using WorksheetFunction.VLookup() seems to make
no
difference. Is this a known bug? Does anyone know how to fix
it?

Here is my function that contains the VLookup() call. As I mentioned, it
seems to work perfectly when called with values where returnCol is 1. Then
strange numeric return values only occur when returnCol=1.

Function validAccount(acctStr As Variant, returnCol As Integer) As String

Dim tempAcct As Variant
Dim row1 As Integer, row2 As Integer
Dim rng As Range

' Find the specified entry in the corresponding data sheet.
row1 =
Workbooks(toolkitBookName).Sheets(SUPPORT_SHEET).C ells(CP_RANGE_ROW1, 2)
row2 =
Workbooks(toolkitBookName).Sheets(SUPPORT_SHEET).C ells(CP_RANGE_ROW2, 2)
Set rng = Workbooks(toolkitBookName).Sheets(ACCT_SHEET). _

Range(Workbooks(toolkitBookName).Sheets(ACCT_SHEET ).Cells(row1, 1), _

Workbooks(toolkitBookName).Sheets(ACCT_SHEET).Cell s(row2, 3))
tempAcct = Application.VLookup(acctStr, rng, returnCol, False)
If IsError(tempAcct) Then
tempAcct = "INVALID"
End If
validAccount = tempAcct

End Function


"Chip Pearson" wrote:

Post the exact code you are using.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JSzymanski" wrote in
message
...
When I call Application.VLookup() with the col_index_num
parameter = 1 and
range_lookup=False, the function returns strange numeric
values -- they look
like something close to a row index for the matching value, but
it's
definitely NOT returning the matching string values in Column 1
of the data
table range being searched. After the call IsError(retVal) is
False, so the
function thinks it has found an exact match. The exact same
call with
col_index_num set to some value 1 works fine and returns the
expected data.
Note also that using WorksheetFunction.VLookup() seems to make
no
difference. Is this a known bug? Does anyone know how to fix
it?

Without knowing what all those other variables are, it's gonna be guesses (for
me anyway).

First thing: I'd change "As Integer" to "As Long" just in case your rows are
too big to fit in Integers.

Second thing: I'd put a bunch of debug.print lines into your code to see if
things are what you expect.

debug.print "------" & vblf & _
acctStr & vblf & _
rng.address(external:=true) & vblf & _
returnCol & vblf & "-------"

tempAcct = Application.VLookup(acctStr, rng, returnCol, False)

You may want to add your CP_Range_row1 (and _row2) and Support_sheet and
ACCT_sheet to the list, too--just in case...

Third thing--and this won't change your program at all--but it may make it
easier to read. You can use the with/end with structure to save some typing:

With Workbooks(toolkitBookName).Sheets(SUPPORT_SHEET)
row1 = .Cells(CP_RANGE_ROW1, 2).Value
row2 = .Cells(CP_RANGE_ROW2, 2).Value
End With

With Workbooks(toolkitBookName).Sheets(ACCT_SHEET)
Set rng = .Range(.Cells(row1, 1), .Cells(row2, 3))
End With

Fourth thing: Any chance that it's just a number format difference? 1E3 = 1000
are the same value, but look different.



JSzymanski wrote:

Here is my function that contains the VLookup() call. As I mentioned, it
seems to work perfectly when called with values where returnCol is 1. Then
strange numeric return values only occur when returnCol=1.

Function validAccount(acctStr As Variant, returnCol As Integer) As String

Dim tempAcct As Variant
Dim row1 As Integer, row2 As Integer
Dim rng As Range

' Find the specified entry in the corresponding data sheet.
row1 =
Workbooks(toolkitBookName).Sheets(SUPPORT_SHEET).C ells(CP_RANGE_ROW1, 2)
row2 =
Workbooks(toolkitBookName).Sheets(SUPPORT_SHEET).C ells(CP_RANGE_ROW2, 2)
Set rng = Workbooks(toolkitBookName).Sheets(ACCT_SHEET). _

Range(Workbooks(toolkitBookName).Sheets(ACCT_SHEET ).Cells(row1, 1), _

Workbooks(toolkitBookName).Sheets(ACCT_SHEET).Cell s(row2, 3))
tempAcct = Application.VLookup(acctStr, rng, returnCol, False)
If IsError(tempAcct) Then
tempAcct = "INVALID"
End If
validAccount = tempAcct

End Function

"Chip Pearson" wrote:

Post the exact code you are using.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JSzymanski" wrote in
message
...
When I call Application.VLookup() with the col_index_num
parameter = 1 and
range_lookup=False, the function returns strange numeric
values -- they look
like something close to a row index for the matching value, but
it's
definitely NOT returning the matching string values in Column 1
of the data
table range being searched. After the call IsError(retVal) is
False, so the
function thinks it has found an exact match. The exact same
call with
col_index_num set to some value 1 works fine and returns the
expected data.
Note also that using WorksheetFunction.VLookup() seems to make
no
difference. Is this a known bug? Does anyone know how to fix
it?





--

Dave Peterson