We help IT Professionals succeed at work.

find the smallest number closest to 0

610 Views
Last Modified: 2013-11-25
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

Open in new window

Comment
Watch Question

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

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

Author

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.

Author

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

Open in new window

I think you shouldn't put line 27.

Write it at line 31 instead :

GetcallMatch = Min

Author

Commented:
Thanks,

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

Author

Commented:
yes, its working perfectly now.

Thanks all
Unlock the solution to this question.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.