Hi all, I am building a code that does a similar job like MATCH INDEX or Vlookup.
but these functions need their inputs to be in an certain order if i want a APPROX match.
so I built my own code.
I worked for a while and now it doesn't anymore...

here is my condition that gives me headache
Sub MPApproximation()
'On Error Resume Next
    'Application.ScreenUpdating = False
    'Application.DisplayAlerts = False
Dim Difference As Double
Dim DifferenceBefore As Double
Dim DifferenceAfter As Double


For i = 4 To 150
    Range("Y" & i).Value = RowBefore

RowBefore = -1
RowAfter = -1
DifferenceAfter = 1000
DifferenceBefore = 1000
'For l = 4 To 10000
'MPReference = Range("O" & l).Value
'If MPReference - LastMPReference > 5 Then
'Newsection = l
'End If
LastMPReference = MPReference

    For j = 4 To 2000
        Difference = (Range("Q" & i).Value - Range("O" & j).Value)
        If Diffenrence <= 0 And Abs(Difference) < Abs(DifferenceBefore) And j >= RowBefore And j < RowBefore + 100 Then
                DifferenceBefore = Difference
                RowBefore = j
                'MsgBox DifferenceBefore
            End If
            
'        If Difference >= 0 And Abs(Difference) < Abs(DifferenceAfter) And j >= 127 Then
'                DifferenceAfter = Difference
'                RowAfter = j
'                'MsgBox DifferenceAfter
'            End If
            
        'If Difference = 0 Then
       ' RowAfter = j
       ' RowBefore = j
       ' MsgBox Difference
       ' End If
    Next j
    
    'MsgBox RowBefore
    'MsgBox Rowafter
    
'    LatAfter = Range("G" & RowAfter).Value
'    LongAfter = Range("H" & RowAfter).Value
    LatBefore = Range("G" & RowBefore).Value
    LongBefore = Range("H" & RowBefore).Value
'    MPAfter = Range("O" & RowAfter).Value
    MPBefore = Range("O" & RowBefore).Value
    
'    Range("R" & i).Value = MPAfter
'    Range("S" & i).Value = LatAfter
'    Range("T" & i).Value = LongAfter
    Range("U" & i).Value = MPBefore
    Range("V" & i).Value = LatBefore
    Range("W" & i).Value = LongBefore
    Range("X" & i).Value = RowBefore

    'Range("Y" & i).Value = DifferenceAfter >= 0
    'Range("Z" & i).Value = DifferenceBefore <= 0

Next i

    For k = 4 To 150
    If Range("X" & k).Value = False Then
    Range("AA" & k).FormulaR1C1 = _
        "=((RC[-5]-RC[-8])/(RC[-6]-RC[-9]))*(RC[-10]-RC[-9])+RC[-8]"
    Range("AB" & k).FormulaR1C1 = _
        "=((RC[-5]-RC[-8])/(RC[-7]-RC[-10]))*(RC[-11]-RC[-10])+RC[-8]"
    Else
    Range("AA" & k).Value = Range("S" & k).Value
    Range("AB" & k).Value = Range("T" & k).Value
        End If
        
    Next k
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "lol"
End Sub
I am debugging it, so there might be some wierd stuff and stuff that are commented. but the real headache part is here:

        If Diffenrence <= 0 And Abs(Difference) < Abs(DifferenceBefore) And j >= RowBefore And j < RowBefore + 100 Then
first and second statement works fine, at the third, it doesn't here is sample result:

622 621
622 622
165 622
165 165
165 165
168 165
170 168

(these are j value, for loop iteration, or row number)

Some BG story.
Its a code to approximate a Milepost depending on the GPS location. the mile post has been approximated depeding on the distance different points has. but the data acquisition is every 10 seconds. so there are holes and we want a more "accurate" approx. so I built something that gets the two closest points (over and under the searching value). after i simply extrapolate the value and search it via the wanted value. the prob is j is not comparing properly..

thank you all!