Selecting a Worksheet Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Coolboy55 - 22 Aug 2005 19:54 GMT

This is my code:

-Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select-

I get the error message: "Method 'Range' of object '_Worksheet'
failed"

It's a runtime error 1004.

I get this error whenever the active sheet is any sheet besides
Sheet4, and using Worksheets("SheetName") in place of Sheet4 doesn't
work either, instead getting an "Application-defined or object-defined
error."

What's going on?

Signature

Coolboy55

Reply to this Message

Bernie Deitrick - 22 Aug 2005 20:34 GMT

Coolboy55,

You can't select a range on an inactive worksheet. Since you rarely need to select, change your
code to get rid of the .Select commands, or change the code to select sheet4 first.

HTH,
Bernie
MS Excel MVP

> This is my code:
>
[quoted text clipped - 11 lines]
>
> What's going on?

Reply to this Message

Bernie Deitrick - 23 Aug 2005 13:17 GMT

Coolboy55,

I should also have mentioned that when you get rid of your select statements, you need to pay
attention to your range objects, since an unqualified range object defaults to the active sheet.
The range in your statement:

Sheet4.Range("A3", Cells(l_LastRow, l_LastColumn)).Select

would be better written (without the select) as

Sheet4.Range("A3", Sheet4.Cells(l_LastRow, l_LastColumn))

HTH,
Bernie
MS Excel MVP

> Coolboy55,
>
[quoted text clipped - 20 lines]
>>
>> What's going on?

Reply to this Message

Coolboy55 - 23 Aug 2005 14:21 GMT

Thank you Bernie! You have been a great help.

What is the usual method for applying borders and shading to certain
cells if not by selecting? I had the feeling I was doing it the long
way, but it didn't occur to me immediately that there was a better way.

Signature

Coolboy55

Reply to this Message

Bernie Deitrick - 23 Aug 2005 15:57 GMT

Coolboy55,

See the sample code below.

HTH,
Bernie
MS Excel MVP

Sub Macro2()
With Worksheets("Sheet2").Range("A1:B10")
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End With
End Sub

> Thank you Bernie! You have been a great help.
>
> What is the usual method for applying borders and shading to certain
> cells if not by selecting? I had the feeling I was doing it the long
> way, but it didn't occur to me immediately that there was a better way.

Reply to this Message

William Horton - 22 Aug 2005 21:46 GMT

You may have to reference the proper sheet again just before Cells(l_Last.....
Try Sheet4.Cells(l_La....
and see if that works.

Bill Horton

> This is my code:
>
[quoted text clipped - 11 lines]
>
> What's going on?

Reply to this Message

William Horton - 22 Aug 2005 21:55 GMT

Sorry....Check that last reply. You have to activate the sheet before you
can select.

> You may have to reference the proper sheet again just before Cells(l_Last.....
> Try Sheet4.Cells(l_La....
[quoted text clipped - 17 lines]
> >
> > What's going on?

Reply to this Message