Referencing A Range Instead of .Selection?

  • Thread starter (PeteCresswell)
  • Start date
P

(PeteCresswell)

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?
 
D

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))


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

(PeteCresswell)

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.)
Click to expand...
You could use:
Click to expand...
With theSS.Worksheets(theDataSheetName)
with .Range(.Cells(3, 10), .Cells(5,22))
Click to expand...

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.
 
J

Jim Cone

I think the issue is the missing ")" on the right end of the line.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"(PeteCresswell)" <[email protected]>
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
 
D

Don Guillett

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
 
Top