VBA练习1:如何实现区间查找

兰色幻想

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>青城山苦丁茶</i>在2007-10-2 22:37:50的发言：</b><br/><p>兰版，这个行不：</p><p>ublic Function cha(ByVal rng As Range)<br/>&nbsp;&nbsp;&nbsp; cha = Format(WorksheetFunction.VLookup(rng, Array(Array(0, 0.01), Array(10001, 0.02), Array(25001, 0.04), Array(50001, 0.05), Array(80001, 0.065), Array(100001, 0.07)), 2, 1), "0.0%")<br/>End Function<br/></p></div><p>挺好!!二维数组的一种表示方法</p>

lpz001

<p>明白啦，谢谢兰色幻想！</p><p>我只是用了您视频教的方法，却没理解透透彻[em04]</p><p>ublic Function cha(ByVal rg As Range)<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; cha = Application.VLookup(rg.Value, [{0,0.01;10001,0.02;25001,0.04;50001,0.05;80001,0.065;100001,0.07}], 2)<br/>&nbsp;&nbsp;&nbsp; <br/>End Function</p>

uranus1997

<p>我的答案，第一个和苦丁茶的一样，第二个原来的中括号我是用大括号的，结果怎么也不成，请教了BF，改成中括号就OK啦：）</p><p>1</p><p>Function cha(rag As Range)<br/>Dim arr As Variant<br/>arr = Array(Array(0, "1%"), Array(10001, "2%"), Array(25001, "4%"), Array(50001, "5%"), Array(80001, "6.50%"), Array(100000, "7%"))<br/>cha = Application.WorksheetFunction.VLookup(rag, arr, 2)<br/>End Function</p><p>2</p><p>Function cha1(rag As Range)<br/>cha1 = Application.WorksheetFunction.VLookup(rag, [{0,"1%";10001,"2%";25001,"4%";50001,"5%";80001,"6.50%";100000,"7%"}], 2)<br/>End Function<br/><br/></p>[em17]

[此贴子已经被兰色幻想于2007-10-3 0:02:28编辑过]

lpz001

哈哈，大家都有了收获！

leyco

我汗，占位置的兄弟也太多了吧~~~~~~~~~~~~~~~~~~~~~~~~~~~~

冠军欧洲2010