I have written a function interpolate_part_size that will not recalculate unless I click in the Excel cell and press Enter - which then gives the correct result. The purpose of the function is to interpolate a value comparing the current row of the active cell, with a reference row.
I have tried Application.Volatile but then it returns the same results on each and every row for the same argument, which is not correct. Using F9 to recalculate doesn't work either.
Any suggestions?
Function interpolate_part_size(pp) As Double
'--- Subroutine to interpolate size
Dim part_size_high As Double
Dim part_size_low As Double
Dim pp_high As Double
Dim pp_low As Double
Dim low_index As Integer
Dim high_index As Integer
Dim current_row As Integer
With Application.WorksheetFunction
current_row = ActiveCell.Row
' match on the range of cells in the current row
high_index = .Match(pp, Worksheets("DATA").Range(Cells(current_row, 4), Cells(current_row, 29)), -1)
low_index = high_index + 1
pp_high = .Index(Worksheets("DATA").Range(Cells(current_row, 4), Cells(current_row, 29)), 1, high_index)
pp_low = .Index(Worksheets("DATA").Range(Cells(current_row, 4), Cells(current_row, 29)), 1, low_index)
part_size_high = .Index(Worksheets("DATA").Range("PSD"), 1, high_index)
part_size_low = .Index(Worksheets("DATA").Range("PSD"), 1, low_index)
End With
If pp_high = pp_low Then
interpolate_part_size = part_size_low
Else:
interpolate_part_size = (pp - pp_low) / (pp_high - pp_low) * (part_size_high - part_size_low) + part_size_low
End If
End Function
