Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Have a value in one sheet (obtained by importing a text file) and want to
look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook .... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Dan wrote:
Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Still no luck. Could be the variables I am passing to VLockUp. Get Script
out of range error. Dim ans As String ans = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2").Range("R3:S1500"), 2, False) MsgBox ans "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Tried a few things and this is what I get:
Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ ..Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Same as last time. Subscript out of range means you don't have a sheet with
that name. You may think you do, but the tab name must have a space in it or something else that makes it different from "test2". Why do you get an error - you tell it to look in a location that doesn't exist. -- Regards, Tom Ogilvy "Dan" wrote in message ... Tried a few things and this is what I get: Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ .Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
It wasn't the test2 that was the issue. I remove the quotes and the function
worked. How do I set up the value to look up, per the example below "2C0h" to a cell in the workbook? "Tom Ogilvy" wrote: Same as last time. Subscript out of range means you don't have a sheet with that name. You may think you do, but the tab name must have a space in it or something else that makes it different from "test2". Why do you get an error - you tell it to look in a location that doesn't exist. -- Regards, Tom Ogilvy "Dan" wrote in message ... Tried a few things and this is what I get: Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ .Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Found it. Set the variable to Range("cell that you want").
"2C0h" = Range("C7") Thanks everyone for all the help "Dan" wrote: It wasn't the test2 that was the issue. I remove the quotes and the function worked. How do I set up the value to look up, per the example below "2C0h" to a cell in the workbook? "Tom Ogilvy" wrote: Same as last time. Subscript out of range means you don't have a sheet with that name. You may think you do, but the tab name must have a space in it or something else that makes it different from "test2". Why do you get an error - you tell it to look in a location that doesn't exist. -- Regards, Tom Ogilvy "Dan" wrote in message ... Tried a few things and this is what I get: Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ .Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
If test2 was a string variable, that would be true, but there is no way to
tell that from your posting. -- Regards, Tom Ogilvy "Dan" wrote in message ... It wasn't the test2 that was the issue. I remove the quotes and the function worked. How do I set up the value to look up, per the example below "2C0h" to a cell in the workbook? "Tom Ogilvy" wrote: Same as last time. Subscript out of range means you don't have a sheet with that name. You may think you do, but the tab name must have a space in it or something else that makes it different from "test2". Why do you get an error - you tell it to look in a location that doesn't exist. -- Regards, Tom Ogilvy "Dan" wrote in message ... Tried a few things and this is what I get: Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ .Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up a value and returning another
Set the variable to Range("cell that you want").
2C0h is a variable? -- Regards, Tom Ogilvy "Dan" wrote in message ... Found it. Set the variable to Range("cell that you want"). "2C0h" = Range("C7") Thanks everyone for all the help "Dan" wrote: It wasn't the test2 that was the issue. I remove the quotes and the function worked. How do I set up the value to look up, per the example below "2C0h" to a cell in the workbook? "Tom Ogilvy" wrote: Same as last time. Subscript out of range means you don't have a sheet with that name. You may think you do, but the tab name must have a space in it or something else that makes it different from "test2". Why do you get an error - you tell it to look in a location that doesn't exist. -- Regards, Tom Ogilvy "Dan" wrote in message ... Tried a few things and this is what I get: Dim ans As String Dim ans2 As String 'This works ans = Application.WorksheetFunction.VLookup("2C0h", Range("R5:S1500"), 2, _ False) MsgBox ans ans2 = Application.WorksheetFunction.VLookup("2C0h", Worksheets("Test2") _ .Range("R5:S1500"), 2, False) MsgBox ans2 Run-time error '9': Subscript out of range Why does adding the sheet name cause a failure? Need to add this since I want to expand the function to lookup in another workbook and worksheet. "Franz Verga" wrote: Dan wrote: Have a value in one sheet (obtained by importing a text file) and want to look up the value (value will start in C7 and continue down column C) in another workbook (B6:B2000). Once I find that value I then need to return a value 2 columns over in the same row (D6:D2000). Here is what I have: DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files, *.txt; *.asc", Title:="Select the Data File") Open DataFileName For Input As #FileNum VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", Title:="Select the VMM File") Workbooks.OpenText Filename:=VMM_FileName VMM_Workbook = ActiveWorkbook Workbooks.Add template:=xlWorksheet Set Data_Workbook = ActiveWorkbook ... code here for importing text file Have tried Match, Find, Index, ... can not get any to work. Need help to find the answer. Hi Dan, try with VLOOKUP... You can use this way: Application.WorksheetFunction.VLookup(Arg1,Arg2,Ar g3,[Arg4]) where you have to specify Arg1,Arg2,Arg3 and Arg4 as it was on a worksheet... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF from Add-In returning #NAME | Excel Worksheet Functions | |||
Returning #N/A | Excel Discussion (Misc queries) | |||
Sum returning 0 | Excel Worksheet Functions | |||
UDF returning #VALUE! why? | Excel Discussion (Misc queries) | |||
If/Then help in returning value | Excel Programming |