| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup table in another workbook
I want to get the vlookup results to a variable, where that table data is in
book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Hi Rachel,
application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Dear Niek,
this does not work, I get this error result that does not really help "Object does not support this property or methos" can you figuer it out? Thanks in advance rachel "Niek Otten" wrote: Hi Rachel, application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Hi Rachel,
Worked OK for me in Excel 2003 Sorry! -- Kind regards, Niek Otten "Rachel" wrote in message ... Dear Niek, this does not work, I get this error result that does not really help "Object does not support this property or methos" can you figuer it out? Thanks in advance rachel "Niek Otten" wrote: Hi Rachel, application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Hi everybody, thanks for the cooperation.
I now have some progress, the error value is 2042, on the line begins with LUResults = (see below) but it does not afect the flow of the macro. Except that later on I have the line MsgBox LUResults there the macro stucks and the error note is type mismatch error no 13 I have excel2000 and windows xp The entire macro is Sub VLU() Dim Taarich As Date ' Taarich is Date in Hebrew Dim LUResults As Variant Dim LupTable As Range Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Tab le01") Taarich = ActiveCell.Offset(0, -1).Value LUResults = Application.VLookup(Taarich, LupTable, 3, True) MsgBox LUResults End Sub Any mor help, please......... rachel "Niek Otten" wrote: Hi Rachel, Worked OK for me in Excel 2003 Sorry! -- Kind regards, Niek Otten "Rachel" wrote in message ... Dear Niek, this does not work, I get this error result that does not really help "Object does not support this property or methos" can you figuer it out? Thanks in advance rachel "Niek Otten" wrote: Hi Rachel, application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Check to see if the =vlookup() returns an error...
if iserror(luresults) then msgbox "not found! else msgbox Luresults end if Rachel wrote: Hi everybody, thanks for the cooperation. I now have some progress, the error value is 2042, on the line begins with LUResults = (see below) but it does not afect the flow of the macro. Except that later on I have the line MsgBox LUResults there the macro stucks and the error note is type mismatch error no 13 I have excel2000 and windows xp The entire macro is Sub VLU() Dim Taarich As Date ' Taarich is Date in Hebrew Dim LUResults As Variant Dim LupTable As Range Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Tab le01") Taarich = ActiveCell.Offset(0, -1).Value LUResults = Application.VLookup(Taarich, LupTable, 3, True) MsgBox LUResults End Sub Any mor help, please......... rachel "Niek Otten" wrote: Hi Rachel, Worked OK for me in Excel 2003 Sorry! -- Kind regards, Niek Otten "Rachel" wrote in message ... Dear Niek, this does not work, I get this error result that does not really help "Object does not support this property or methos" can you figuer it out? Thanks in advance rachel "Niek Otten" wrote: Hi Rachel, application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Sub VLU()
Dim Taarich As Date ' Taarich is Date in Hebrew Dim LUResults As Variant Dim LupTable As Range Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Tab le01") Taarich = ActiveCell.Offset(0, -1).Value LUResults = Application.VLookup(Taarich, LupTable, 3, True) if iserror(LUResults) then msgbox Taarich & " was not found" else MsgBox LUResults end if End Sub 2042 is equivalent to #N/A returned in the worksheet -- Regards, Tom Ogilvy "Rachel" wrote in message ... Hi everybody, thanks for the cooperation. I now have some progress, the error value is 2042, on the line begins with LUResults = (see below) but it does not afect the flow of the macro. Except that later on I have the line MsgBox LUResults there the macro stucks and the error note is type mismatch error no 13 I have excel2000 and windows xp The entire macro is Sub VLU() Dim Taarich As Date ' Taarich is Date in Hebrew Dim LUResults As Variant Dim LupTable As Range Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Tab le01") Taarich = ActiveCell.Offset(0, -1).Value LUResults = Application.VLookup(Taarich, LupTable, 3, True) MsgBox LUResults End Sub Any mor help, please......... rachel "Niek Otten" wrote: Hi Rachel, Worked OK for me in Excel 2003 Sorry! -- Kind regards, Niek Otten "Rachel" wrote in message ... Dear Niek, this does not work, I get this error result that does not really help "Object does not support this property or methos" can you figuer it out? Thanks in advance rachel "Niek Otten" wrote: Hi Rachel, application.WorksheetFunction.VLookup(workbooks("b ook1").worksheets("sheet1" ).range("a1"),workbooks("book2").worksheets("sheet 1").range("table01"),2) -- Kind regards, Niek Otten "Rachel" wrote in message ... I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
I find using variables make it a bit simpler to debug.
dim LookUpTable as range dim LookUpCell as Range dim res as variant set lookupTable = workbooks("book2.xls").worksheets("sheet1").range( "table01") set lookupCell = workbooks("book1.xls").worksheets("sheet1").range( "a1") res = application.vlookup(lookupcell.value, lookuprng, 2) 'or for an exact match: res = application.vlookup(lookupcell.value, lookuprng, 2, false) if iserror(res) then 'error was returned, what happens? else msgbox res end if === And by using application.vlookup() instead of application.worksheetfunction.vlookup(), I can test the result to see if it returned an error. And I think it's always a good idea to include the extension of the file (assuming that book1 and book2 have both been saved). Rachel wrote: I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
Hi everybody, thanks for trying to help
What I may not put clear is, that for testing my vba procedure I chose a date that is in the table01 col A and still get an error no 2042 while writing the function interactively =VLOOKUP(A5,Book2.xls!Table01,3,FALSE) I get the expected results Any Idea? Thanks rachel "Dave Peterson" wrote: I find using variables make it a bit simpler to debug. dim LookUpTable as range dim LookUpCell as Range dim res as variant set lookupTable = workbooks("book2.xls").worksheets("sheet1").range( "table01") set lookupCell = workbooks("book1.xls").worksheets("sheet1").range( "a1") res = application.vlookup(lookupcell.value, lookuprng, 2) 'or for an exact match: res = application.vlookup(lookupcell.value, lookuprng, 2, false) if iserror(res) then 'error was returned, what happens? else msgbox res end if === And by using application.vlookup() instead of application.worksheetfunction.vlookup(), I can test the result to see if it returned an error. And I think it's always a good idea to include the extension of the file (assuming that book1 and book2 have both been saved). Rachel wrote: I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup table in another workbook
convert your date to a long in the vlookup. This usually works better:
Sub VLU() Dim Taarich As Date ' Taarich is Date in Hebrew Dim LUResults As Variant Dim LupTable As Range Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Tab le01") Taarich = ActiveCell.Offset(0, -1).Value LUResults = Application.VLookup(clng(Taarich), LupTable, 3, True) if iserror(LUResults) then msgbox Taarich & " was not found" else MsgBox LUResults end if End Sub -- Regards, Tom Ogilvy "Rachel" wrote in message ... Hi everybody, thanks for trying to help What I may not put clear is, that for testing my vba procedure I chose a date that is in the table01 col A and still get an error no 2042 while writing the function interactively =VLOOKUP(A5,Book2.xls!Table01,3,FALSE) I get the expected results Any Idea? Thanks rachel "Dave Peterson" wrote: I find using variables make it a bit simpler to debug. dim LookUpTable as range dim LookUpCell as Range dim res as variant set lookupTable = workbooks("book2.xls").worksheets("sheet1").range( "table01") set lookupCell = workbooks("book1.xls").worksheets("sheet1").range( "a1") res = application.vlookup(lookupcell.value, lookuprng, 2) 'or for an exact match: res = application.vlookup(lookupcell.value, lookuprng, 2, false) if iserror(res) then 'error was returned, what happens? else msgbox res end if === And by using application.vlookup() instead of application.worksheetfunction.vlookup(), I can test the result to see if it returned an error. And I think it's always a good idea to include the extension of the file (assuming that book1 and book2 have both been saved). Rachel wrote: I want to get the vlookup results to a variable, where that table data is in book2 and the key search is in book1 somthing like Vlook = application.worksheet.vlookup(book1!sheet1[a1], book2!sheet1[table01], 2) this does not work. Both worbooks are in the same directory, and I use excel 2000 and windoew xp thanks rachel -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refresh pivot table in workbook A when changing a cell in workbook | Excel Worksheet Functions | |||
| vlookup other workbook | Excel Discussion (Misc queries) | |||
| How to copy pivot table workbook to new workbook? | New Users to Excel | |||
| Open Workbook - Select Range as table for vlookup | Excel Discussion (Misc queries) | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
