convert formula for vba use

How can I convert the match formula to do it in vba and assign the value to variable?

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

RayneAuthor Commented:
Or any other excel formula that can be done in vba for the same purpose
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.
RayneAuthor Commented:
awesome Sire, thank you
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.