# UDF Interpolating to Ignore Empty Cells

#### BAILEYSAN

##### New Member
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### c_m

##### Well-known Member
baileysan,
Welcome to the Board.

#### BAILEYSAN

##### New Member
Code:
``````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

##### MrExcel MVP
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)")

Replies
2
Views
213
Replies
3
Views
97
Replies
6
Views
594
Replies
2
Views
155
Replies
0
Views
1K

### Forum statistics

1,191,718
Messages
5,988,275
Members
440,146
Latest member
rgomes8 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back