vlookup

Namrata Prashar

Hello

can anyone help me with how to write the excel vlookup function in vba

the function in excel looks like this

VLOOKUP(O2,'[Consolidated list of supplier.xls]Sheet3'!$A$5:$F$218,6,FALSE)

how can i get something similar working in a macro.

thanks

namrata

Andy Pope


Hi,

How about some like this

Dim rngLookupValue As Range
Dim rngtable As Range
Dim lngColIndex As Long
Dim blnRangeLookup As Boolean

Set rngLookupValue = Range("O2")
Set rngtable = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$218")
lngColIndex = 6
blnRangeLookup = False

vntResult = Application.WorksheetFunction.VLookup(rngLookupValue, rngtable, lngColIndex, blnRangeLookup)

Namrata Prashar

thanks Andy for the reply.

i tried writing something similar to what you suggested but i get error. i wrote

Public LookupValue As Range
Public TableArray As Range

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281") ............... error here

The error is

Run Time error 9

Sub-Script out of range

can you please help

thanks

namrata

Andy Pope

What does the variable MyPath contain

If you have a file C:\MyFolder\book1.xls open it's name will be book1.xls. It will not include the path.

FinAnalyst

Namrata,

Is MyPath the name of the workbook, or is it a variable If it is the name of a workbook, then put "MyPath" in quotations.

Namrata Prashar

hello all,

MYPath is a variable which contains the name of the excel file i want to open for vlookup. it is in a different folder and not in the one where the original excel is (the one i am writing macro in).

can you now help me with why this error.

namrata

Namrata Prashar

hello,

 

the whole code that i have written is as follows

 

Public LookupValue As Range
Public TableArray As Range
Public MyPath As String

Sub VlookUp_Click()

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281")

End Sub

 

namrata

Andy Pope

If the file is open then you do not need the full path as the name only the filename and extension.

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

MyFile = "Consolidated list of supplier.xls"

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyFile).Worksheets("Sheet3").Range("$A$5:$F$281")

Namrata Prashar

hi,

i modified the code as below but it gives the following error ..

run time error 1004

unable to get the vlookup property of the worksheet function class

my new code looks something like this.

**************************************************************************

Sub VlookUp_Click()

Dim LookupValue As Range
Dim TableArray As Range
Dim ColIndex As Integer
Dim RangeLookup As Boolean
Dim MyPath As String

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

Workbooks.Open Filename:=MyPath, UpdateLinks:=0, ReadOnly:=True
Set TableArray = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$281")
ActiveWorkbook.Close SaveChanges:=False

Set LookupValue = Sheet3.Range("O2")
RangeLookup = False
ColIndex = 6

Sheet3.Cells(2, 16) = Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

End Sub
**************************************************************************************

can somebody please help me with fixing the bug !!

thanks

namrata

Andy Pope

What happens if you wait until after the lookup before closing the workbook

Sheet3.Cells(2, 16) = Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

ActiveWorkbook.Close SaveChanges:=False

Namrata Prashar

buddy, the lookup is used in a different excel worksheet and the 'consolidated supplier.xls' is a different file. if i do not close the consolidated.xls there will be again a problem of active worksheet as the macro is not being written in the consolidated.xls... its just being used for the lookup range purpose.

Andy Pope

You could fully qualify this reference

Set LookupValue = Sheet3.Range("O2")

The 1004 error will be raised if not match is found. Are you sure the value you are looking for is in the lookup table

Namrata Prashar

yes, the lookup value is there .... when i try to use the vlookup directly in the excel sheet for the same set of values it works fine but it is giving this error here when coded in VBA

Andy Pope

I'm not sure what to suggest next.

Any chance of emailing an example set of workbooks

Namrata Prashar

also,

when i replace the code as

Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)
the error changes to "can not find the vlookup function in the worksheetfunction class" but if i write

Application.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

the error changes to "1004:application defined or object defined error"

any clue ....

i have fully qualified the range also as follows

Set LookupValue = ThisWorkbook.Sheets("Data Sheet").Range("O2")

namrata