Match function / returning closest value in VBA

[lecoughlin]

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.

 

[Sean Timmons]

Sounds like you just need a ROUND

IF ROUND(cost,0) = value Then Exit FOR

 

[PBezucha]

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 I’s.