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
|