Solved

convert formula for vba use

Posted on 2014-03-13
4
370 Views
Last Modified: 2014-03-13
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
0
Comment
Question by:Rayne
  • 2
4 Comments
 

Author Comment

by:Rayne
ID: 39928130
Or any other excel formula that can be done in vba for the same purpose
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39928138
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
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39928145
You can use this in VBA

Evaluate("=MATCH(2,INDEX(1/(A1:A9=""Apple""),0))")
0
 

Author Closing Comment

by:Rayne
ID: 39928161
awesome Sire, thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question