Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using worksheet functions INDEX and MATCH within a macro
I would like to use a worksheet function in a macro. This is how I would do
it if I were entering it straight into a cell: =INDEX(range1,MATCH(F1,range2,0)) Can anyone tell me how I would enter it into the macro? The F1 would change to "cboProductSelect.Value", but other than that it should be simple, right? *hopeful* I found this reference, but haven't managed to get it working by trying to follow the guidelines as my formula has too many differences: http://msdn.microsoft.com/library/de...HV05205786.asp |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using worksheet functions INDEX and MATCH within a macro
Hi
It depends, how range1 and range2 are is defined. When range1 and range2 are cell range references, then (on fly, and you have to check in MS Help, are Index and Match supported in VBA as worksheet functions) like this: x=Application.WorksheetsFunction.Index(Worksheets( "SheetName1").Range("A2:A1 00"),Application.WorksheetsFunction.Match(cboProdu ctSelect.Value,Worksheets( "SheetName2").Range("X2:X100),0)) When range1 and range2 are named ranges, then probably (I myself have called to named ranges returning values only, but maybe it works with ranges too): x=Application.WorksheetsFunction.Index([range1],Application.WorksheetsFuncti on.Match(cboProductSelect.Value,[range2],0)) Another option is to write the combo choice to some cell (p.e. the same F1), and define the result of formula calculation as Named Value, p.e. MyValue MyValue=INDEX(range1,MATCH(SheetName$F$1,range2,0) ) Now you can refer to MyValue in VBA as x=[MyValue] -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "JurgenBrea" wrote in message ... I would like to use a worksheet function in a macro. This is how I would do it if I were entering it straight into a cell: =INDEX(range1,MATCH(F1,range2,0)) Can anyone tell me how I would enter it into the macro? The F1 would change to "cboProductSelect.Value", but other than that it should be simple, right? *hopeful* I found this reference, but haven't managed to get it working by trying to follow the guidelines as my formula has too many differences: http://msdn.microsoft.com/library/de...HV05205786.asp |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using worksheet functions INDEX and MATCH within a macro
Hi
The easiest way to check how to write a formula in code is to use the macro recorder and see how it would do it after I changed it a little I got the following: ActiveCell.FormulaR1C1 = "=INDEX(range1,MATCH(" & cboProductSelect.Value & ",range2,0))" The only other way is use the application."formula" therefore it would look like: ActiveCell.Formula = Application.Index(range1, Application.Match(" & cboProductSelect.Value & ", range2, 0)) The only problem with Application."formula" is that it puts the result into the spreadsheet rather than the formula! Hopefully I have understood what you are asking for. James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using worksheet functions INDEX and MATCH within a macro
dim res as variant
dim myVal as variant res = application.match(cboProductSelect.Value, _ worksheets("sheet1").range("a1:a999"), 0) if iserror(res) then msgbox "No match found" else myval = worksheets("sheet1").range("b1:b999")(res) end if I wasn't sure what range1 and range2 were. JurgenBrea wrote: I would like to use a worksheet function in a macro. This is how I would do it if I were entering it straight into a cell: =INDEX(range1,MATCH(F1,range2,0)) Can anyone tell me how I would enter it into the macro? The F1 would change to "cboProductSelect.Value", but other than that it should be simple, right? *hopeful* I found this reference, but haven't managed to get it working by trying to follow the guidelines as my formula has too many differences: http://msdn.microsoft.com/library/de...HV05205786.asp -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX and MATCH functions | Excel Worksheet Functions | |||
Index and Match functions I think | New Users to Excel | |||
Using Index & Match functions to find data on separate worksheet. | Excel Worksheet Functions | |||
How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions | |||
Using Index and Match functions to lookup data in another worksheet | Excel Programming |