Unqualified ranges usually refer to the activesheet. If mybook.worksheets(1)
isn't the active sheet, then your second example will blow up real good.

You could use

Set sourceRange = mybook.Worksheets(1) _
.Range(mybook.Worksheets(1).Cells(1, 1), _
mybook.Worksheets(1).Cells(lrow, lcol)))

But it's less typing and easier to understand to do something like:

with mybook.worksheets(1)
Set sourceRange = .Range(.Cells(1, 1), .Cells(lrow, lcol)))
end with

The dots in front of the range objects (range() and cells()) mean that they
belong to the object in the previous With statement--this time
mybook.worksheets(1).

===
Just to note...

When you use the same kind of unqualified ranges behind a worksheet, then that
unqualified range won't belong to the activesheet--they'll belong to the sheet
that owns the code.

I find it always less painful to qualify the ranges.

Even if I have to do:

dim wks as worksheet
set wks = activesheet

with wks
...

By using a variable that represents a worksheet, I get VBA's intellisense, too!


justme wrote:

Hi,

How come this works:
Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)

but this doesn't:
Set sourceRange = mybook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol)))

I get object defined error.

here are the functions I'm using:

Function LastRow(sh As Worksheet)
' Find the last real row
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
' Find the last real column
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

End Function

lrow and lcol both dimmed as long in my sub.

Thank you much.

--

Dave Peterson