We help IT Professionals succeed at work.

# find the smallest number closest to 0

on
610 Views
under this code. I tried to search a value which is smaller than 0.07, but here is my problem, are there any ways we can find the smallest value which is the most closest to 0.

thanks

Function GetcallMatch(No As Double, lots As Integer) 'This is function for delta-neutral call
Dim FirstDataRow As Long
Dim LastDataRow As Long
Dim CurrentDataRow As Long

Dim ResultRow As Long
Dim ResultCol As Integer

Dim Result As Integer

'MsgBox No

FirstDataRow = 14  'first row of call
LastDataRow = 54 'lastrow of call

For CurrentDataRow = FirstDataRow To LastDataRow
If Abs(No - lots * Worksheets("BS-IV").Cells(CurrentDataRow, 7)) <= 0.07 Then
GetcallMatch = Worksheets("BS-IV").Cells(CurrentDataRow, 1)
CurrentDataRow = LastDataRow + 1
End If
Next CurrentDataRow

End Function
Comment
Watch Question

## View Solutions Only

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
CERTIFIED EXPERT

Commented:
Can you pls post an example of your file. Black Scholes?
Cheers
Dave

Commented:
I am not yet figuring out how to post my file, sorry, its a bit complicated to explain the whole file, but what i wanted with this code is really to search the minimal value, once it scan the whole value from FirstDataRow to LastDataRow. I should be able to retrive a value which coressponding to the minimal value from the list.

Commented:
This is my latest code, I think its allright. what do you think?
Function GetcallMatch(No As Double, lots As Integer) 'This is function for delta-neutral call
Dim FirstDataRow As Long
Dim LastDataRow As Long
Dim CurrentDataRow As Long

Dim Min As Long
Dim ResultRow As Long
Dim ResultCol As Integer

Dim Result As Integer

FirstDataRow = 14   'first row of call
LastDataRow = 54 'lastrow of call

Min = Abs(No - (lots * Worksheets("BS-IV").Cells(FirstDataRow, 7)))

For CurrentDataRow = FirstDataRow + 1 To LastDataRow

If Abs(No - lots * Worksheets("BS-IV").Cells(CurrentDataRow, 7)) < Min Then

Min = Abs(No - lots * Worksheets("BS-IV").Cells(CurrentDataRow, 7))

GetcallMatch = Worksheets("BS-IV").Cells(CurrentDataRow, 1)

End If
Next CurrentDataRow

End Function

Commented:
I think you shouldn't put line 27.

Write it at line 31 instead :

GetcallMatch = Min

Commented:
Thanks,

Yes, i put them on purpose, because once i fatched the min value, i need another value coressponding to that particular row.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
yes, its working perfectly now.

Thanks all
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.