Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

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

Index Function in VBA
 

Moved a few things around, based on the rest of the code, moved these
statements

VMM_FileName = Application.GetOpenFilename(fileFilter:="DBF Files, *.dbf", _
Title:="Select the VMM File")
Workbooks.OpenText Filename:=VMM_FileName
Set oWB = ActiveWorkbook

Do to the code, but still does not return a response. I get a script out of
range error.


"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

Index Function in VBA
 

Yes, put a break on the first code line (select it then F9), and when it
breaks, step a line at a time with F8

--
HTH

Bob Phillips

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

"Dan" wrote in message
...
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

Index Function in VBA
 

I meant the execution of the internal code itself. The step by step function
of the execution of this line

res = Application.Match(Range("C7"), rng.Offset(0, -2), 0)

Using F8 only jumps from line to line for execution. I would like to see the
execution of each part of the line.


"Bob Phillips" wrote:

Yes, put a break on the first code line (select it then F9), and when it
breaks, step a line at a time with F8

--
HTH

Bob Phillips

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

"Dan" wrote in message
...
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

Index Function in VBA
 

You would have to stick that in a cell and evaluate the cell using F9.

--
HTH

Bob Phillips

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

"Dan" wrote in message
...
I meant the execution of the internal code itself. The step by step
function
of the execution of this line

res = Application.Match(Range("C7"), rng.Offset(0, -2), 0)

Using F8 only jumps from line to line for execution. I would like to see
the
execution of each part of the line.


"Bob Phillips" wrote:

Yes, put a break on the first code line (select it then F9), and when it
breaks, step a line at a time with F8

--
HTH

Bob Phillips

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

"Dan" wrote in message
...
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