I am trying to write a function in VBA that linearly interpolates between values in a 2D table. So for a given target value in the first column the function will read the values in the first column and return an interpolated value from the associated values in the second column. I wanted to simplify the input arguments to just the target value (TargetValue) and the range of the table (ArrayRange).

Any example table is here.

Independent Variable Dependent Variable
0 1
50 1.3
200 1.4

So, if I wanted to interpolate 65 the value would be 1.31. Calculation: (((1.4 - 1.3)/(200 - 50) * (65 - 50)) + 1.3).


Here is my code:
Function Interpolate(TargetValue As Variant, ArrayRange As Range) As Variant
'   TargetValue: interpolation value, ArrayRange: range location of table to be interpolated.
    Dim i_in As Variant, i_d As Variant, f_in As Variant, f_d As Variant
'   Value in table greater than TargetValue is labelled as final_independent, its associated dependent is labelled as final_dependent.
'   Value smaller than TargetValue is labelled initial_independent etc.
'   i: initial, f: final, in: independent, d: dependent.
    i_in = WorksheetFunction.VLookup(TargetValue, ArrayRange, 1)
    i_d = WorksheetFunction.VLookup(TargetValue, ArrayRange, 2)
    Do While WorksheetFunction.HLookup(i_in, ArrayRange, 1, False) = "#N/A"
        ArrayRange = Range("ArrayRange").Offset(1, 0)
'   Problem 
   Loop
    f_in = WorksheetFunction.HLookup(i_in, ArrayRange, 2, False)
    f_d = WorksheetFunction.HLookup(i_d, ArrayRange, 2, False)
    Dim l_f As Variant
'   l_f: linear factor
    If (i_in = TargetValue) Then
        Interpolate = i_d
    Else
        l_f = (f_d - i_d) / (f_in - i_in)
        Interpolate = i_d + (TargetValue - i_in) * l_f
    End If
End Function
To linearly interpolate I need the values of the two independent variables above and below the target value (i_in and f_in), in the table, and their associated dependent variables (i_d and f_d). As it is a 2D table it is easy to obtain i_in and i_d using the VLOOKUP function, as omitting the final argument makes VLOOKUP find the largest value below the target value 'rounds down' (or the functions first argument).

The difficulties begin when I attempt to find the values of f_in and f_d. VLOOKUP can't be used as it only rounds down, so I set out to use HLOOKUP in conjunction with it (As an aside - if there is a way to get VLOOKUP to round up then I wouldn't have to go into the messy business of HLOOKUP and loops...). However, as VLOOKUP only reads the values in the first column of a range, HLOOKUP does the same with the first row (which incidentally means that the function works for target values less than the 2nd entry in column 1 of the table). But I want the function to work for any target value between the first entry in the first column of the table and the last. Hence I added a Do While loop.

I wanted the loop to move the range down by one row (using the Offset(rows,cols) function) every time that the value returned by the VLOOKUP was not found in the top row of the range (I understand that HLOOKUP returns a #N/A in this instance). Hence, when the value was found in a row, it would then become the first row of the new range and the HLOOKUP function could be used successfully to return the values of f_in and f_d. That was the plan, however, for some reason excel doesn't like the way I coded it and so this is where I need some help or advice. I suspect that the problem lies with the Offset loop - but am not certain.

I've been trying to get a working function for the past few days with varying amounts of success; using various other structures and strategies - but I think that this is the closest I have got so far. Perhaps a completely different tack would be better - maybe using the MATCH and/or INDEX functions (I've only looked into them briefly)?

I started VBA programming last Friday, so a moderate amount of patience with any 'obvious' idiocies would be appreciated.

Thanks in advance for any help.