vlookup

vlookup

• Ramanuj

  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")

  
  
  
  
  
  

• suneelb2b

  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

  
  
  

• Jacco Mintjes

  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.

  
  

• PaulMD

  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)
  
  
  
  

• chiwa737

  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
  
  
  

• Wedgetail

  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.
  
  

• TusharSinha

  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
  
  

• NikiB

  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

  
  
  

• ShivaanKeldon

  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

  
  
  

• mthomasq3

  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

  
  
  

• enric vives

  I'm not sure what to suggest next.
  
  Any chance of emailing an example set of workbooks
  
  
  

• bingbangzoom

  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.
  
  
  

• uwcodeman

  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
  
  
  

• Armela

  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

  
  
  
• vlookup