Solved

convert formula for vba use

Posted on 2014-03-13
4
367 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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