Referencing A Range Instead of .Selection?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

(PeteCresswell) - 08 Dec 2006 23:58 GMT

This works - but I know now that it's bad practice:
----------------------------------------------------------------------
4002 Dim myCosmeticRange As Excel.Range

5420 With theSS.Worksheets(theDataSheetName)
5424 Set myCosmeticRange = .Range(.Cells(3, 10), .Cells(5,22)
5426 End With
5429 myCosmeticRange.Select

5430 With theSS.Selection
5431 .Borders(xlEdgeTop).Weight = xlThick
5432 .Borders(xlEdgeTop).LineStyle = xlContinuous
5433 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
5469 End With
----------------------------------------------------------------------

Seems like this should work, but the compiler doesn't buy the first line:
----------------------------------------------------------------------
5420 With theSS.Worksheets(theDataSheetName).Range(.Cells(3, 10), .Cells(5,22)
5431 .Borders(xlEdgeTop).Weight = xlThick
5432 .Borders(xlEdgeTop).LineStyle = xlContinuous
5433 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
5439 End with
----------------------------------------------------------------------

I'm guessing I have the right idea - make the references to an object - but
what's the right syntax?

Signature

PeteCresswell

Reply to this Message

Dave Peterson - 09 Dec 2006 00:36 GMT

The .cells() in this line:
With theSS.Worksheets(theDataSheetName).Range(.Cells(3, 10), .Cells(5,22))
refer to the previous with statement. (You didn't share that portion.)

You could use:

With theSS.Worksheets(theDataSheetName)
with .Range(.Cells(3, 10), .Cells(5,22))

Now, the .range() and both .cells() refers to theSS.worksheets(thedatasheetname)
worksheet.

> This works - but I know now that it's bad practice:
> ----------------------------------------------------------------------
[quoted text clipped - 25 lines]
> --
> PeteCresswell

Signature

Dave Peterson

Reply to this Message

(PeteCresswell) - 09 Dec 2006 01:51 GMT

Per Dave Peterson:

>The .cells() in this line:
>With theSS.Worksheets(theDataSheetName).Range(.Cells(3, 10), .Cells(5,22))
>refer to the previous with statement. (You didn't share that portion.)

>You could use:

> With theSS.Worksheets(theDataSheetName)
> with .Range(.Cells(3, 10), .Cells(5,22))

There was none. I was trying to make the reference stand by itself - so there
was no previous statement to refer to - which is consistent with the error I was
getting (something about object required...).

I'm not in the habit of nesting With statements - no particular reason, probably
just blew up on me at some time.

Your example makes sense to me and I'll give it a shot when I get back to the
code tomorrow.

Signature

PeteCresswell

Reply to this Message

Jim Cone - 09 Dec 2006 02:49 GMT

I think the issue is the missing ")" on the right end of the line.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"(PeteCresswell)" <x@y.Invalid>
wrote in message
This works - but I know now that it's bad practice:
----------------------------------------------------------------------
4002 Dim myCosmeticRange As Excel.Range

5420 With theSS.Worksheets(theDataSheetName)
5424 Set myCosmeticRange = .Range(.Cells(3, 10), .Cells(5,22)
5426 End With
5429 myCosmeticRange.Select

5430 With theSS.Selection
5431 .Borders(xlEdgeTop).Weight = xlThick
5432 .Borders(xlEdgeTop).LineStyle = xlContinuous
5433 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
5469 End With
----------------------------------------------------------------------
Seems like this should work, but the compiler doesn't buy the first line:
----------------------------------------------------------------------
5420 With theSS.Worksheets(theDataSheetName).Range(.Cells(3, 10), .Cells(5,22)
5431 .Borders(xlEdgeTop).Weight = xlThick
5432 .Borders(xlEdgeTop).LineStyle = xlContinuous
5433 .Borders(xlEdgeTop).ColorIndex = xlAutomatic
5439 End with
----------------------------------------------------------------------
I'm guessing I have the right idea - make the references to an object - but
what's the right syntax?
--
PeteCresswell

Reply to this Message

Don Guillett - 09 Dec 2006 16:17 GMT

Are you sure you want thick?
This should be all you need. If you want the whole range use
.borders.Weight = xlThick

Sub doborders()
Sheets("sheet12").Range(Cells(3, 10), Cells(5, 22)) _
.borders(xlEdgeTop).Weight = xlThick
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> This works - but I know now that it's bad practice:
> ----------------------------------------------------------------------
[quoted text clipped - 25 lines]
> but
> what's the right syntax?

Reply to this Message

Rate this thread: