# Insert Text representing an array into a custom function

#### MylesMc

##### New member
Hey guys,

I have attached a spreadsheet where the current problem I'm working on involves data found on Columns Y through AK. I have an output table that I need to run some interpolation calculations on. The attached spreadsheet is an exerpt of a larger spreadsheet that is too big to attach to this forum. It contains a module (module1.bas) for an interpolation equation. The function is named interp. The arguement is interp(X range, Y Range, X Value) and returns a Y value at the X value interpolated from the X and Y ranges.

I now have text representing a range (example text: M227:M263) that has been concatenated from calculations performed to get the correct ranges of data that needs to be interpolated over. I need a way to place this concatenated text into my interp function and get a valid answer (=interp(M227:M263,N227:N263,W5). simply placing the cell containing the text string representing the range does not work. I need this to be dynamic such that cells in columns AI and AJ can autofill and the functions within the cells contain the correct ranges as currently shown in columns AE through AH.

Any help is appreciated.

The code for the interp function contained in module1.bas is shown below and will have to be manually added to the spreadsheet:

Code:
``````Function interp(havecol As Range, wantcol As Range, have As Variant)
'count how many values are in the interpolation range
k = 1
Do While (havecol.Cells(k, 1).Value <> 0)
k = k + 1
Loop
k = k - 1
'find where in the column the value is
'as soon as reach a value greater, stop--i is the place above

If have < havecol.Cells(k, 1).Value Then
If have > havecol.Cells(1, 1).Value Then
i = 1
Do While (have > havecol.Cells(i, 1).Value)
i = i + 1
Loop
valhi = havecol.Cells(i, 1).Value
vallo = havecol.Cells(i - 1, 1).Value
lookhi = wantcol.Cells(i, 1).Value
looklo = wantcol.Cells(i - 1, 1).Value
interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
Else

'extrapolate off the low end...
If have <> 0 Then
'            interp = wantcol.Cells(1, 1).Value
valhi = havecol.Cells(2, 1).Value
vallo = havecol.Cells(1, 1).Value
lookhi = wantcol.Cells(2, 1).Value
looklo = wantcol.Cells(1, 1).Value
interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
Else
interp = 0
End If
End If
Else
'extrapolate off the high end...
valhi = havecol.Cells(k, 1).Value
vallo = havecol.Cells(k - 1, 1).Value
lookhi = wantcol.Cells(k, 1).Value
looklo = wantcol.Cells(k - 1, 1).Value
interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
End If
End Function``````
 Thanks, Myles Mc

#### Attachments

• ExcelGuru Posting.xlsm
346.9 KB · Views: 13
Last edited by a moderator:

#### Andy Pope

##### New member
You can use the INDIRECT function

so the formula would look something like,

=interp(INDIRECT(AE5),INDIRECT(AF5),W5)

Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

=interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)

#### JoePublic

##### Super Moderator
Staff member
Your formulas are a little confusing as the ranges currently in the formulas don't appear to match the text ranges, but basically you need INDIRECT:
=interp(INDIRECT(AE5),INDIRECT(AF5),W5)
for example.

#### MylesMc

##### New member
Worked a treat, Thanks!
I love learning new functions! Also, thanks to Joe Public for providing a very similar response.

Yes, I see that I had an error in my spreadsheet example, good eyes!

You can use the INDIRECT function

so the formula would look something like,

=interp(INDIRECT(AE5),INDIRECT(AF5),W5)

Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

=interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)