CODEWITHSUNDEEP
vbaexcel
user1860694
user1860694

Reputation: 67

Passing range objects to functions in excel-vba -- receive "Object Required" error

I'm trying to build a function that will take an input cell (say "B5") and return a range object that refers to a table (of which the supplied cell is the top-right entry)

Sub Macro1()
Dim testCell As Range

testCell = Worksheets("HMA").Range("B5")
ReturnTable testCell

End Sub

Function ReturnTable(cell As Range)
    firstcell = cell
    lastrow = firstcell.End(x1Down)
    Table = Range(firstcell, lastrow + 5).Value
End Function

I've been running into a lot of problems here, and I feel like I'm missing something simple. The error that I'm getting is "Object Required" at the lastRow line.

Looking through it in debug mode I see that testCell is assigned the value of the range object (I assume this is the default). What am I missing here?

Is my approach even sound? Should I be looking at solving this problem in a different way?

Upvotes: 2

Views: 23728

Answers (2)

deramko
deramko

Reputation: 2835

When you assign Objects (like Range) you have to use "Set".

If you assign Range without "Set" VBA will assign the value of the Range and not the Range itself.

Set testCell = Worksheets("HMA").Range("B5")

Upvotes: 3

Daniel Möller
Daniel Möller

Reputation: 86600

The End returns a Range object, so, lastrow must be a range variable.

Function ReturnTable(firstcell as Range) as Range 'add this as range to tell the result of the function is a range
    dim LastCell as Range
    Set LastCell = firstcell.END(xldown)
    Set ReturnTable = Range(firstcell, lastcell) 'must use the same name of the function
End Function

If you want 5 cells below lastcell, use LastCell.Offset(5,0)

And in Macro1, you probably will want something like

Set SomeVar = ReturnTable(testcell)

Upvotes: 4

Related Questions