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