Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match function / returning closest value in VBA
Hi All,
Please see the simplified example below. For i = 1 To 5 cost = (i * apple) / pear If cost = value Then Exit For next i On the second line, instead of exiting the loop when cost is EQUAL to value, I'd like to write code that would exit when it's the CLOSEST to that value. For example, if value was 4.3 I would like it to exit when cost = 4. If this was an array I was working with, I think I would use the match function. Is this possible? Any insight would be greatly appreciated! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match function / returning closest value in VBA
Sounds like you just need a ROUND
IF ROUND(cost,0) = value Then Exit FOR " wrote: Hi All, Please see the simplified example below. For i = 1 To 5 cost = (i * apple) / pear If cost = value Then Exit For next i On the second line, instead of exiting the loop when cost is EQUAL to value, I'd like to write code that would exit when it's the CLOSEST to that value. For example, if value was 4.3 I would like it to exit when cost = 4. If this was an array I was working with, I think I would use the match function. Is this possible? Any insight would be greatly appreciated! Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match function / returning closest value in VBA
Or more programmatically for a more intricate case
Dif1 = 100000 For I = 1 To N F = funct(I, Apple, Pear) €˜ie: F = I * Apple / Pear Dif2 = Abs(Val €“ F) If Dif2 < Dif1 Then MinF = F Dif1 = Dif2 ElseIf Dif2 Dif1 Then Exit For End If Next I You can step out of the loop only provided you are sure (as this is the case) that the progression of funct is monotonous, otherwise you have to go through all Is. -- Petr Bezucha "Dana DeLouis" wrote: For i = 1 To 5 cost = (i * apple) / pear Would this idea work instead? Sub Demo() Dim apple, pear, Val Dim Closest Dim k apple = 3 pear = 4 Val = 28 '// Number of loops k = (Sqr((8 * pear * Val) / apple + 1) - 1) / 2 '// Round # of loops k = Round(k) Closest = (apple * k * (1 + k)) / (2 * pear) End Sub -- HTH :) Dana DeLouis wrote in message ... Hi All, Please see the simplified example below. For i = 1 To 5 cost = (i * apple) / pear If cost = value Then Exit For next i On the second line, instead of exiting the loop when cost is EQUAL to value, I'd like to write code that would exit when it's the CLOSEST to that value. For example, if value was 4.3 I would like it to exit when cost = 4. If this was an array I was working with, I think I would use the match function. Is this possible? Any insight would be greatly appreciated! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closest Match Formula | Excel Discussion (Misc queries) | |||
index and match closest | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
closest match | Excel Worksheet Functions | |||
Closest number match help ... | Excel Worksheet Functions |