Apply worksheet function ROW to the result of a VBA

Question

I would like to define a function RangeDown in VBA. It finds a header in the second range argument, and returns the range under the header within the third range argument. Here is my current code:

Function RangeDown(header, range_header, range_data)
    i = 0
    row_header = 0
    col_header = 0
    For Each Cell In range_header
        If Cell.Value = header Then
            i = i + 1
            row_header = Cell.Row
            col_header = Cell.Column
        End If
    Next Cell
    If i = 0 Then
        RangeDown = "Cannot find the header"
    ElseIf i > 1 Then
        RangeDown = "Found more than one matching headers"
    Else
        lastRow = range_data.Row + range_data.Rows.Count - 1
        If row_header >= lastRow Then
            RangeDown = "No Range"
        Else
            Set r = Range(Cells(row_header + 1, col_header), Cells(lastRow, col_header))
            Set x = Application.Intersect(r, range_data)
            RangeDown = x
        End If
    End If
 
End Function

In general, the above code works. However, I just realized that we cannot apply the function ROW to the result of RangeDown, Formulas like =LET(x, RangeDown("header4",C5:H5,C3:H9), ROW(x)) return #VALUE!:

Does anyone know how to amend the VBA code such that we could apply ROW on the result?

You are not using `Set`, so you are returning the [value](https://stackoverflow.com/a/26891780/11683) of the range, not the range itself. — GSerg, Jan 13 '22 at 07:48

Ike · Accepted Answer · 2022-01-13T08:30:33.540

1

The UDFRangeDown has to return a range.

As @GSerg said, you have to use SET to return the range.

PLUS you have to amend your formula like this:

=LET(x,RangeDown("header 6", C5:H5,C3:H9),IFERROR(ROW(x),x))

In case RangeDown returns the error text, ROW will fail (IFERROR = true) and the result of x (= error message) will be returned.

Function RangeDown(header As String, _
                    range_header As Range, _
                    range_data As Range) As Variant
    i = 0
    row_header = 0
    col_header = 0
    For Each Cell In range_header
        If Cell.Value = header Then
            i = i + 1
            row_header = Cell.Row
            col_header = Cell.Column
        End If
    Next Cell
    If i = 0 Then
        RangeDown = "Cannot find the header"
    ElseIf i > 1 Then
        RangeDown = "Found more than one matching headers"
    Else
        lastRow = range_data.Row + range_data.Rows.Count - 1
        If row_header >= lastRow Then
            RangeDown = "No Range"
        Else
            Set r = Range(range_header.Worksheet.Cells(row_header + 1, col_header), range_header.Worksheet.Cells(lastRow, col_header))
            Set x = Application.Intersect(r, range_data)
            Set RangeDown = x
        End If
    End If
End Function

edited Jan 13 '22 at 08:30

answered Jan 13 '22 at 07:38

Ike

9,580
4
13
29

A Variant can contain a Range. Not declaring the function as Range explicitly is not the source of the problem. – GSerg Jan 13 '22 at 07:44
Thank you. For the error text, is it possible to make a cell range containing the text? I still want to return some readable information. – SoftTimur Jan 13 '22 at 07:46
@GSerg: your are right - main source of the problem is the missing SET. If keeping the variant, it's possible to keep the error messages - I will update the code – Ike Jan 13 '22 at 08:06
Thank you. I found a bug in my initial macro. The `Set r` part should be `Set r = Range(range_header.Worksheet.Cells(row_header + 1, col_header), range_header.Worksheet.Cells(lastRow, col_header))`. You could update your answer if you want. – SoftTimur Jan 13 '22 at 08:29
I updated the code accordingly – Ike Jan 13 '22 at 08:30

Apply worksheet function ROW to the result of a VBA

1 Answers1