convert formula for vba use

How can I convert the match formula to do it in vba and assign the value to variable?
=MATCH(2,INDEX(1/(A1:A9="Apple"),0))
http://stackoverflow.com/questions/21270293/excel-vba-find-fist-and-last-occurrence-of-a-particular-value-in-a-column

Thank
RayneAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
You can use this in VBA

Evaluate("=MATCH(2,INDEX(1/(A1:A9=""Apple""),0))")
0
 
RayneAuthor Commented:
Or any other excel formula that can be done in vba for the same purpose
0
 
Harry LeeCommented:
You can first define a named range for the data, then enter the formula using named range.

    ActiveWorkbook.Names.Add Name:="DataRng", RefersToR1C1:="=Sheet1!R1C1:R9C1"
    Range("C2").FormulaR1C1 = "=MATCH(2,INDEX(1/(DataRng=""Apple""),0))"

Open in new window


Using named range makes your life much easier.
0
 
RayneAuthor Commented:
awesome Sire, thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.