SPILL RESULTS AS ARGUMENT FOR VBA FUNCTION

[demariscal]

I am trying to use the spilled results from a "FILTER" function in a spreadsheet and pass it as an argument to a VBA Function, but it is not working. Please help. Thank you.

 

[Joe4]

Welcome to the Board!

Please show us what is being returned on your sheet, and the VBA code you are trying to use it in.

 

[Rick Rothstein]

I am trying to use the spilled results from a "FILTER" function in a spreadsheet and pass it as an argument to a VBA Function, but it is not working. Please help. Thank you.

Doing what Joe4 suggested would be helpful (keep it in mind for future questions you may ask), but if I can anticipate what your problem is... let's say you have this formula in cell A1...

=SEQUENCE(10)

and inside your VBA code you wanted to assign the number of elements in this spilled formula to a variable named HowMany, this is how you would reference the spilled range (note the # sign)...

HowMany = Range("A1#").Count

 

[demariscal]

Welcome to the Board!

Please show us what is being returned on your sheet, and the VBA code you are trying to use it in.

I am getting #VALUE.

Public Function Interpolate(x As Double, Table As Variant, Optional NSel As Double = 2, Optional Way As String = "C", Optional iOrder As Single = 1) As Double
Dim cLast As Integer, rLast As Integer
Dim FirstVal As Integer, LastVal As Integer
Dim i As Long
i = 1

cLast = Table.Rows.Count
rLast = Table.Columns.Count

'Interpolation can be done at any order
'Select which way the table is set up as: Columns or Rrows
Select Case Way
    Case "C"
   
        If Table(1, 1) > Table(cLast, 1) Then
          
            'Descending
            Select Case x
                Case Is <= Table(cLast, 1): dem_Interpolate = Table(cLast, NSel)
                Case Is >= Table(1, 1): dem_Interpolate = Table(1, NSel)
                'Case Is < Table(1, 1): dem_Interpolate = "X too Small"
                'Case Is > Table(cLast, 1): dem_Interpolate = "X too High"
                Case Else
                    Do While x < Table(i, 1) And i < cLast
                        i = i + 1
                    Loop
                    dem_Interpolate = Table(i - 1, NSel) + (x ^ iOrder - Table(i - 1, 1) ^ iOrder) * _
                    (Table(i, NSel) - Table(i - 1, NSel)) / (Table(i, 1) ^ iOrder - Table(i - 1, 1) ^ iOrder)
            End Select
        Else
            Select Case x
                'Ascending
                Case Is <= Table(1, 1): dem_Interpolate = Table(1, NSel)
                Case Is >= Table(cLast, 1): dem_Interpolate = Table(cLast, NSel)
                'Case Is < Table(1, 1): dem_Interpolate = "X too Small"
                'Case Is > Table(cLast, 1): dem_Interpolate = "X too High"
                Case Else
                    Do While x > Table(i, 1) And i < cLast
                        i = i + 1
                    Loop
                    dem_Interpolate = Table(i - 1, NSel) + (x ^ iOrder - Table(i - 1, 1) ^ iOrder) * _
                    (Table(i, NSel) - Table(i - 1, NSel)) / (Table(i, 1) ^ iOrder - Table(i - 1, 1) ^ iOrder)
          
            End Select
    End If

Case "R"
    Select Case x
        Case Is <= Table(1, 1): dem_Interpolate = Table(NSel, 1)
        Case Is >= Table(1, rLast): dem_Interpolate = Table(NSel, rLast)
        'Case Is < Table(1, 1): dem_Interpolate = "X too Small"
        'Case Is > Table(1, rLast): dem_Interpolate = "X too High"
        Case Else
            Do While x > Table(1, i) And i < rLast
                i = i + 1
            Loop
            dem_Interpolate = Table(NSel, i - 1) + (x ^ iOrder - Table(1, i - 1) ^ iOrder) * _
            (Table(NSel, i) - Table(NSel, i - 1)) / (Table(1, i) ^ iOrder - Table(1, i - 1) ^ iOrder)
    End Select

End Select

End Function

 

[demariscal]

Welcome to the Board!

Please show us what is being returned on your sheet, and the VBA code you are trying to use it in.

Thank you, I have looked everywhere and couldn't find a solution.

 

[Joe4]

When posting VBA code, please use Code Tags. It makes the code much more readable. See here for how: How to Post Your VBA Code
I updated the code in your previous post so it is more readable.

Can you please post us a sample of your data, where you are using this FILTER function?
And then show us where you are trying to use this function you have created?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Rick Rothstein]

Which argument in your code is the spilled range from a FILTER function being assigned to... the Table argument? If so, a spilled range is not a table, it is just a range, nothing more. If not the Table argument, then which one?

 

[demariscal]

Which argument in your code is the spilled range from a FILTER function being assigned to... the Table argument? If so, a spilled range is not a table, it is just a range, nothing more. If not the Table argument, then which one?

The argument "Table".

 

[Rick Rothstein]

The argument "Table".

How are you referencing the spilled array from the FILTER function when you call your function on the worksheet? Also, it will be helpful for you to follow the advice Joe4 gave you above as it will make our job of helping you much easier.

 

[demariscal]

How are you referencing the spilled array from the FILTER function when you call your function on the worksheet? Also, it will be helpful for you to follow the advice Joe4 gave you above as it will make our job of helping you much easier.

=dem_Interpolate(D20,FILTER(RESULTS2!$N$4:$X$4723,RESULTS2!$P$4:$P$4723=$AB$15),6)