![]() |
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? |
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? |
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? |
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? |
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