Help with "WITH"

[Pellechi]

To Anyone,

I'm attempting to Left Justify the contents of a single cell AND underline
it's contents at the same time,
followed by Right Justifying the contents of ANOTHER cell AND underlining
it's contents.

Both cells are within the same RANGE but at different offset's from the base
of the RANGE .
I thought I could use the following code - but it doesn't appear to be
working properly ...
Everything is LEFT justifying ...

'--------------------------------------------
Sub Add_Underline_Staff_Number()
' Sheet (offset) begins at the cell 1 line below
' SHIFT_CPC_Number_Chart_BASE (Currently: cell F5) of Daily Sheet
Sheets("Daily Sheet").Select
Range("SHIFT_CPC_Number_Chart_BASE").Activate
Range("SHIFT_CPC_Number_Chart_BASE").Offset(Range("BODIES_MIN_DAY"),
0).Select
' Left Justify Cell's contents
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
' Underline Cell's contents
With .Font
.underline = xlUnderlineStyleSingle
End With
End With
Range("SHIFT_CPC_Number_Chart_BASE").Activate
Range("SHIFT_CPC_Number_Chart_BASE").Offset(Range("BODIES_MIN_EVE"),
0).Select ' EVES Number
' Right Justify Cell's contents
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
' Underline Cell's contents
With .Font
.underline = xlUnderlineStyleSingle
End With
End With
Range("A3").Select
End Sub

'--------------------------------------------

Any help would be greatly appreciated,

Jim Pellechi

 

[Tom Ogilvy]

Suspect the values contained in Range("BODIES_MIN_DAY") and
Range("BODIES_MIN_EVE), used as arguments to the offset command do not
contain the numbers you intend.

Also, Note that offset from a range refers to a range of the same size.

Range("A1:C1").Offset(2,0) refers to A3:C3 as an example.

Since no one has any ideas what your named ranges refer to or to know what
you are trying to refer to, it is difficult to say what the exact problem
might be.

 

[Pellechi]

Tom,

Found the problem on the line:
Range("SHIFT_CPC_Number_Chart_BASE").Activate

I changed FROM .Activate TO .Select
and the code works as expected ...

I thought .Activate was proper but apparently not ...

JimP