Inconsistent error select range?! Driving me crazy.

[Webtechie]

I have programmed many routines in which I need to dynamically select a range.

However, lately, I am getting an error message when selection a range.

Sub myCode()

dim lRow as long
dim lCol as long
dim wks as worksheet
dim wb as workbook
dim myRng as range


set wb = thisworkbook
set wks = wb.sheets("recordset")
lRow = wks.range("a1").end(xlDown).row
lCol = wks.range("a1").end(xlToRight).column

set myRng = wks.range(cells(1,1), cells(lRow, lCol))

Sometimes the above statement is causing a error

"Run time error '1004'"

Method 'Range' of object '_Worksheet' failed

I am using Excel XP.

Can anybody makes sense of this and why I get this error sometimes?

Thanks.

 

[Howard31]

Hi webtechie,
Try the following

With wks
set myRng = .range(.cells(1,1), .cells(lRow, lCol))
End With

Don't fotget the dot before both .Cells()

This will make sure that the cells actually refere to wks

Hope this helps,

 

[Webtechie]

Howard31,

Bingo. That worked. Now my question is why? It looks the same to me.
Using the with statement does what?

Thanks for helping.

Tony

 

[Howard31]

Hi Webtchie,

It's not the With statement that does the trick, rather it's the fact that
you qualify the Cells object with the intended Sheet object regardless of
which sheet is currently active.
So the following line of code will actually be the same as using the code I
wrote to you last time only you'll have to repeat the wks 3 times the with
statment makes it clearer and easeir to write.
----------------------------------------------------------------------------
set myRng = wks.range(wks.cells(1,1), wks.cells(lRow, lCol))
-------------------------------------------------------------------------------
With wks
set myRng = .range(.cells(1,1), .cells(lRow, lCol))
End With

--------------------------------------------------------------------------------

Both sets of codes will do the same thing.

Hope I was clear enough

Let me know if I can be of further help!

 

[Webtechie]

Howard31,

Thanks. That makes sense.

I was thinking the cells were associated with wks since, they were inside
the range statement. I see that you have to qualify the range and the cells.

Thanks again for explaining that. The remaining hair on my head are very
thankful because they were about to be pulled as well!

Tony