Looking up a value and returning another

D

Dan

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
 
F

Franz Verga

Dan said:
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.
Click to expand...
Hi Dan,

try with VLOOKUP... You can use this way:

Application.WorksheetFunction.VLookup(Arg1,Arg2,Arg3,[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
 
D

Dan

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 said:
Dan said:
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.
Click to expand...
Hi Dan,

try with VLOOKUP... You can use this way:

Application.WorksheetFunction.VLookup(Arg1,Arg2,Arg3,[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
Click to expand...
 
D

Dan

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 said:
Dan said:
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.
Click to expand...
Hi Dan,

try with VLOOKUP... You can use this way:

Application.WorksheetFunction.VLookup(Arg1,Arg2,Arg3,[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
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
Dan said:
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.
Click to expand...
Hi Dan,

try with VLOOKUP... You can use this way:

Application.WorksheetFunction.VLookup(Arg1,Arg2,Arg3,[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
Click to expand...
Click to expand...
 
D

Dan

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 said:
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 said:
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 said:
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,Arg3,[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
Click to expand...
Click to expand...
Click to expand...
 
D

Dan

Found it. Set the variable to Range("cell that you want").

"2C0h" = Range("C7")

Thanks everyone for all the help

Dan said:
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 said:
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 said:
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.


:

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,Arg3,[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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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.


:

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,Arg3,[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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Set the variable to Range("cell that you want").

2C0h is a variable?

--
Regards,
Tom Ogilvy

Dan said:
Found it. Set the variable to Range("cell that you want").

"2C0h" = Range("C7")

Thanks everyone for all the help

Dan said:
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 said:
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


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.


:

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,Arg3,[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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assigning Name to new workbook 3
excel 2003 vs 2007 menu and sheet issues 3
Importing Text file (Again) 3
Importing Text file 4
Merging Macro - GetOpenFilename & Workbooks.OpenText 2
Copy method of Worksheet class failed? 1
Combine Text Files into One Worksheet 1
Combine Multiple Text Files into One Worksheet 2

Top