Object Required Error in VB Excel Program that Passes Range to a function

Hi Friends,

I am trying to pass the formula which gives a range of cells to a function called concatenate_Text which is defined as

Public Function Concatenate_Text(Textrange As Range) As String
Dim str_text As String
Dim var_cell As Variant
    For Each var_cell In Textrange
        str_text = str_text & " " & var_cell
    Next
    Concatenate_Text = str_text
End Function

The macro that passes the range is this

Dim TopCell As Range
TopCell = "OFFSET('Data Tab'!$A$2,0,$B$16 - 1,INDEX($B$1:$B$15,$B$16,1),1))"
Concatenate_Text (TopCell)

When I try to execute this, it gives me an error saying "Object Required" error. The catch is "I want to send the results of the formula to the function Concatenate_Text ........
Kindly explain where I am going wrong.

Thanks in advance for your time

Change the last line to:

Concatenate_Text TopCell

without the parentheses.

I didn't read the line above - you can't assign a range based on a formula like that. Best guess would be:

Set TopCell = sheets("Data Tab").Range("A2").Offset(0, Range("B16").Value - 1).Resize(Range("B1:B15").cells(Range("B16").Value,1), 1)

Wow, Thank you so much Romperstomper.. It is working super cool