Code:
--------------------
Function FindNearestValue(StartColumn As Long, StartRow As Long, EndColumn As Long, EndRow As Long, SearchValue As Double) As Double

Dim ClosestValue As Double
Dim Diffrence As Double

Dim I As Long, J As Long

' for smart dicks filling in lower end as start values
If StartColumn < 1 Or StartRow < 1 Or EndColumn < 1 Or EndRow < 1 Then
MsgBox "De rij en kolom nummers moeten 1 of hoger zijn." ' <- omdat je ook nederlands bent
End If

If StartColumn EndColumn Then
I = EndColumn 'switch values
StartColumn = EndColumn
EndColumn = I
End If

If StartRow EndRow Then
I = EndRow 'switch values
StartRow = EndRow
EndRow = I
End If
' set the startvalue as far as possible from the search value
If SearchValue <= 0 Then
FindNearestValue = SearchValue + 1.79769313486231E+308
Else
FindNearestValue = SearchValue - 1.79769313486231E+308
End If

Diffrence = Abs(SearchValue - FindNearestValue) ' Abs makes the value positive.

For I = StartColumn To EndColumn 'this wil create a counter I through all columns
For J = StartRow To EndRow ' the same with rows
If Len(ActiveSheet.Cells(I, J).Value) 0 Then 'this will detect for values only and ignore empty or text fields
' convert everything to positive to avoid sign hazards
If Abs(Abs(ActiveSheet.Cells(I, J).Value) - Abs(SearchValue)) < Diffrence Then
Diffrence = (Abs(ActiveSheet.Cells(I, J).Value) - Abs(SearchValue)) ' store this diffrence
FindNearestValue = ActiveSheet.Cells(I, J).Value ' store the value in the return variable
End If
End If
Next
Next

End Function
--------------------


paste it in a vba module (left ALT F11) and insert-module
save the file and close the editor
you will find it in your userdefined functions


--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182
View this thread: http://www.excelforum.com/showthread...hreadid=396420