Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
Index Function Help | New Users to Excel | |||
HELP with INDEX function | Excel Discussion (Misc queries) | |||
Index Function/Match Function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |