using worksheet functions INDEX and MATCH within a macro

[JurgenBrea]

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/d...l/xlhowUsingWorksheetFunctions_HV05205786.asp

 

[Arvi Laanemets]

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(cboProductSelect.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]

 

[Bunter_22]

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

 

[Dave Peterson]

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.