title: Excel Linear Interpolation VBA
body: <p>this function interpolates/extrapolates a table of known x,y
  For example,</p>
  
  <pre><code>x y
  1 10
  2 15
  3 20
  </code></pre>
  
  <p>Linterp(A1:B3, -1) = 0</p>
  
  <p>However, this code can only do two adjacent arrays.
  I would like to modify this code so that I can
  select two separate arrays, for example N106:N109,P106:P109.
  How can I make this adjustment in this code?</p>
  
  <pre><code>Function Linterp(r As Range, x As Double) As Double
       ' linear interpolator / extrapolator
       ' R is a two-column range containing known x, known y
      Dim lR As Long, l1 As Long, l2 As Long
      Dim nR As Long
       'If x = 1.5 Then Stop
  
      nR = r.Rows.Count
      If nR &lt; 2 Then Exit Function
  
      If x &lt; r(1, 1) Then ' x &lt; xmin, extrapolate
          l1 = 1: l2 = 2: GoTo Interp
  
      ElseIf x &gt; r(nR, 1) Then ' x &gt; xmax, extrapolate
          l1 = nR - 1: l2 = nR: GoTo Interp
  
      Else
           ' a binary search would be better here
          For lR = 1 To nR
              If r(lR, 1) = x Then ' x is exact from table
                  Linterp = r(lR, 2)
                  Exit Function
  
              ElseIf r(lR, 1) &gt; x Then ' x is between tabulated values, interpolate
                  l1 = lR: l2 = lR - 1: GoTo Interp
  
              End If
          Next
      End If
  
  Interp:
      Linterp = r(l1, 2) _
      + (r(l2, 2) - r(l1, 2)) _
      * (x - r(l1, 1)) _
      / (r(l2, 1) - r(l1, 1))
  
  End Function
  </code></pre>