Adjacent Cell of closest Match using Index and Match Functions

Question

I have two columns: time and position and need to find the corresponding time the position closest to a variable.

time: Range B:B position: Range C:C variable = target

I've been able to do it on excel using the following formula, but when I do it on Macros (insert Application.WorksheetFunction in front of Index, Match, and Min) I get error

‘Run-time error ‘13’.

I've listed the data as Ranges. Not sure what else to do.

=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))

can you add the exact code that you used with Application.WorksheetFunction — Gangula

Viktor West Viktor West · Accepted Answer · 2020-09-22T11:46:27

It was surprisingly difficult for me to find a solution. First of all in vba cannot be calculated a vector as a difference of two other vectors (like data-value). I hope it will work for you.

Sub mm()
    Dim rdata As Range, rvalue As Range
    Set rdata = Range("A1:A20")
    Set rvalue = Range("B1:B20")
    Dim i As Long
    Dim dt As Variant, vl As Variant, AbsDelta As Variant
    dt = Application.WorksheetFunction.Transpose(rdata.value)
    vl = Application.WorksheetFunction.Transpose(rvalue.value)
    AbsDelta = Application.WorksheetFunction.Transpose(rdata.value) 'just for reserve place 
    For i = LBound(dt) To UBound(dt)
        AbsDelta(i) = Abs(dt(i) - vl(i)) 'This line is updated
    Next i
    Dim minAbsDelta As Double
    minAbsDelta = Application.WorksheetFunction.Min(AbsDelta)
    Range("C1").value = Application.WorksheetFunction.Index(dt, Application.WorksheetFunction.Match( _
        minAbsDelta, AbsDelta, 0))
End Sub

Adjacent Cell of closest Match using Index and Match Functions

1 Answers