ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VLOOKUP() Bug in VBA? (https://www.excelbanter.com/excel-programming/345643-vlookup-bug-vba.html)

JSzymanski

VLOOKUP() Bug in VBA?
 
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?


Tom Ogilvy

VLOOKUP() Bug in VBA?
 
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?




Chip Pearson

VLOOKUP() Bug in VBA?
 
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?




JSzymanski

VLOOKUP() Bug in VBA?
 
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

VLOOKUP() Bug in VBA?
 
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


All times are GMT +1. The time now is 03:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com