The code runs but in the Locals window res = Error 2042 and therefore no
value is returned.

Could the problem be "C7" variable? Since each time this is run a new
Workbook is opened and therefore incremented, WorkBook = Sheetn; n=1,2,3, ...

Is there a way to step through the equation itself as it is running?

"Bob Phillips" wrote:

Try this

Dim VMM_FileName
Dim oWB As Workbook
VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", _
Title:="Select the VMM File")

Workbooks.OpenText Filename:=VMM_FileName
Set oWB = ActiveWorkbook
Dim rng As Range, rng1 As Range
Dim res As Variant
Set rng = oWB.Worksheets(1).Range("D8:D2000")
res = Application.Match(Range("C7"), rng.Offset(0, -2), 0)
If Not IsError(res) Then
MsgBox Worksheets("VMM_FileName").Range("D8:D2000").Cells (res, 1)
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
The workbook VMM_FileName was opened with the following:

VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf",
_
Title:="Select the VMM File")

Workbooks.OpenText FileName:=VMM_FileName

Both the workbook and the worksheet both have the name defined by
VMM_FileName.



"Bob Phillips" wrote:

This worked for me

Dim rng As Range, rng1 As Range
Dim res As Variant
Set rng = Worksheets("VMM_FileName").Range("D8:D2000")
res = Application.Match(Range("C7"), rng.Offset(0, -2), 0)
If Not IsError(res) Then
MsgBox Worksheets("VMM_FileName").Range("D8:D2000").Cells (res, 1)
End If

so does this

Dim ans As Variant
ans = _

Evaluate("INDEX('VMM_FileName'!$D$8:$D$2000,(MATCH (C7,'VMM_FileName'!$B$8:$B
$2000,0)))")
If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

It sounds to me that you don't have a worksheet named VMM_Filename.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
The first code causes a "Run-time error '9': Subscript out of range"
error
at
line
set rng = Worksheets("VMM_FileName").Range("D8:D2000")

The second code runs but does not return a value. If I type the
equation
into a cell the value is returned.


"Tom Ogilvy" wrote:

Dim rng as Range, rng1 as Range
dim res as Variant
set rng = Worksheets("VMM_FileName").Range("D8:D2000")
res = Application.Match(Range("C7"),rng.offset(0,-2),0)
if not iserror(res) then
set rng1 = rng(res)
msgbox rng1.Value & " Address: " & rng1.Address
else
msgbox "Not found"
End if

or

Dim ans as Variant
ans =


Evaluate("INDEX('VMM_FileName'!$D$8:$D$2000,(MATCH (C7,'VMM_FileName'!$B$8:$B
$2000,0)))")
if not iserror(ans) then
msgbox ans
else
msgbox "Not found"
End if

--
Regards,
Tom Ogilvy


"Dan" wrote in message
...
Want to use the following index function in a macro.



=INDEX('VMM_FileName'!$D$8:$D$2000,(MATCH(C7,'VMM_ FileName'!$B$8:$B$2000,0))
)

Do I need to assign the value to a variable then place the result
in a
cell.
The cell for the response is not static, it will vary as more
responses
are
obtained.

Thanks