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.

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.

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