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

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

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

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

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

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