UDF Interpolating to Ignore Empty Cells

[BAILEYSAN]

G'day,

I've got a UDF that interpolates data in a range. The problem with it is that if a part of the range does not contain data, it doesn't perform the calculation. Is there a way to firstly cull the range data and then do the math? Vlookup can work with a range of say C:D but this UDF can't.

 

[c_m]

baileysan,
Welcome to the Board.

Can you post your UDF?

 

[BAILEYSAN]

Function VLin_Interp(Entry, Table As Range, Col)
'
'  This function performs a linear interpolation between rows
'  of data in a table.  The "Entry" value is located in the first
'  column of the table, then an appropriate value from another column
'  in the table (specified by "Col") is interpolated and returned.
'  The value of "Col" must be equal to or greater than 2, and less
'  than the total number of columns in the table.
'  If "Entry" is outside the defined span of the first column
'  the function returns #VALUE.
'  Note that the method used requires that the first column
'  contains values that either increase or decrease monotonically.
'  It this condition isn't satisfied the function returns #VALUE
'
Dim NR, NC, i As Integer
'
NR = Table.Rows.Count
NC = Table.Columns.Count
If NR < 2 Or NC < 2 Or Col < 2 Then
    VLin_Interp = [#VALUE!]
    Exit Function
    End If
If NC < Col Then
    VLin_Interp = [#VALUE!]
    Exit Function
    End If
If Table(1, 1) < Table(NR, 1) Then
    i = 2
    Do While i <= NR
        If Table(i, 1) < Table(i - 1, 1) Then
            VLin_Interp = [#VALUE!]
            Exit Function
            End If
        i = i + 1
        Loop
    If Entry < Table(1, 1) Then
        VLin_Interp = [#VALUE!]
        Exit Function
        End If
    If Entry = Table(1, 1) Then
        VLin_Interp = Table(1, Col)
        Exit Function
        End If
    If Entry > Table(NR, 1) Then
        VLin_Interp = [#VALUE!]
        Exit Function
        End If
    If Entry = Table(NR, 1) Then
        VLin_Interp = Table(NR, Col)
        Exit Function
        End If
    End If
If Table(1, 1) > Table(NR, 1) Then
    i = 2
    Do While i <= NR
        If Table(i, 1) > Table(i - 1, 1) Then
            VLin_Interp = [#VALUE!]
            Exit Function
            End If
        i = i + 1
        Loop
    If Entry > Table(1, 1) Then
        VLin_Interp = [#VALUE!]
        Exit Function
        End If
    If Entry = Table(1, 1) Then
        VLin_Interp = Table(1, Col)
        Exit Function
        End If
    If Entry < Table(NR, 1) Then
        VLin_Interp = [#VALUE!]
        Exit Function
        End If
    If Entry = Table(NR, 1) Then
        VLin_Interp = Table(NR, Col)
        Exit Function
        End If
    End If
i = 2
If Table(1, 1) < Table(NR, 1) Then
    Do While Entry > Table(i, 1)
        i = i + 1
        Loop
    Else
        Do While Entry < Table(i, 1)
            i = i + 1
            Loop
    End If
VLin_Interp = Table(i - 1, Col) + _
   (Table(i, Col) - Table(i - 1, Col)) * _
    (Entry - Table(i - 1, 1)) / _
    (Table(i, 1) - Table(i - 1, 1))
    Exit Function
VLin_Interp = [#NUM!]
End Function

 

[mikerickson]

If you have a VLOOKUP formula that works, you can use it from VBA with (or return it from a UDF)

UDF_Result = Evaluate(" VLOOKUP(working spreadsheet formula)")