problem referring to Range and Cells
 

The program halts and I get a Run-time error '1004' when I run the followin
code from Sheet1. But it works fine when I run it from Sheet2. Any ideas

Thanks

Kevi

Sub test(
Dim Message As Strin
Message = MsgBox("This test # 1"
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5)
Message = MsgBox("This test # 2"
With .Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Wit
End Sub

problem referring to Range and Cells
 

Your code only works for the ActiveSheet.
You say i works fine form Sheet 2 - Try select Sheet 1 before running it on
sheet 2 and then sheet1

This will work:

Sub test()
Worksheets("Sheet1").Activate
With Range(Cells(3, 1), Cells(3, 5))
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub


Cheers,
Flemming


"Kevin" wrote in message
...
The program halts and I get a Run-time error '1004' when I run the
following
code from Sheet1. But it works fine when I run it from Sheet2. Any
ideas?

Thanks.

Kevin

Sub test()
Dim Message As String
Message = MsgBox("This test # 1")
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

problem referring to Range and Cells
 

I would like to affect Sheet2 without activating it. That is, I woul
like to affect Sheet2 when Sheet1 is the active sheet. It seems t
me "Range(Cells(3, 1), Cells(3, 5))" is the problem

Thanks

Kevi

----- Flemming Dahl wrote: ----

Your code only works for the ActiveSheet
You say i works fine form Sheet 2 - Try select Sheet 1 before running it o
sheet 2 and then sheet

This will work

Sub test(
Worksheets("Sheet1").Activat
With Range(Cells(3, 1), Cells(3, 5)
With .Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Wit
End Su


Cheers
Flemmin


"Kevin" wrote in messag
..
The program halts and I get a Run-time error '1004' when I run th
followin
code from Sheet1. But it works fine when I run it from Sheet2. An
ideas
Thanks
Kevi
Sub test(
Dim Message As Strin
Message = MsgBox("This test # 1"
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5)
Message = MsgBox("This test # 2"
With .Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Wit
End Su

problem referring to Range and Cells
 

Yes "Range(Cells(3, 1), Cells(3, 5))" is the problem.

Try this:

Sub test()
With Worksheets("Sheet2").Range(Range(Cells(3, 1), Cells(3,
5)).Address).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Cheers,
Flemming



"Kevin" wrote in message
...
I would like to affect Sheet2 without activating it. That is, I would
like to affect Sheet2 when Sheet1 is the active sheet. It seems to
me "Range(Cells(3, 1), Cells(3, 5))" is the problem.

Thanks.

Kevin

----- Flemming Dahl wrote: -----

Your code only works for the ActiveSheet.
You say i works fine form Sheet 2 - Try select Sheet 1 before running
it on
sheet 2 and then sheet1

This will work:

Sub test()
Worksheets("Sheet1").Activate
With Range(Cells(3, 1), Cells(3, 5))
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub


Cheers,
Flemming


"Kevin" wrote in message
...
The program halts and I get a Run-time error '1004' when I run the
following
code from Sheet1. But it works fine when I run it from Sheet2.
Any
ideas?
Thanks.
Kevin
Sub test()
Dim Message As String
Message = MsgBox("This test # 1")
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

problem referring to Range and Cells
 

Kevin,

The problem is in the With line, as the Calls property is not qualified, so
it refers to the activesheet.

Try this version

Sub test()
Dim Message As String
Message = MsgBox("This test # 1")
With Worksheets("Sheet2")
With .Range(.Cells(3, 1), .Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin" wrote in message
...
The program halts and I get a Run-time error '1004' when I run the
following
code from Sheet1. But it works fine when I run it from Sheet2. Any
ideas?

Thanks.

Kevin

Sub test()
Dim Message As String
Message = MsgBox("This test # 1")
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

problem referring to Range and Cells
 

Work like a charm

Thanks very much Flemming

----- Flemming Dahl wrote: ----

Yes "Range(Cells(3, 1), Cells(3, 5))" is the problem

Try this

Sub test(
With Worksheets("Sheet2").Range(Range(Cells(3, 1), Cells(3
5)).Address).Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Su

Cheers
Flemmin



"Kevin" wrote in messag
..
I would like to affect Sheet2 without activating it. That is, I woul
like to affect Sheet2 when Sheet1 is the active sheet. It seems t
me "Range(Cells(3, 1), Cells(3, 5))" is the problem
Thanks
Kevi
----- Flemming Dahl wrote: ----
Your code only works for the ActiveSheet
You say i works fine form Sheet 2 - Try select Sheet 1 before runnin
it o
sheet 2 and then sheet
This will work
Sub test(
Worksheets("Sheet1").Activat
With Range(Cells(3, 1), Cells(3, 5)
With .Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Wit
End Su
Cheers
Flemmin
"Kevin" wrote in messag
..
The program halts and I get a Run-time error '1004' when I run th
followin
code from Sheet1. But it works fine when I run it from Sheet2
An
ideas
Thanks
Kevi
Sub test(
Dim Message As Strin
Message = MsgBox("This test # 1"
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5)
Message = MsgBox("This test # 2"
With .Borders(xlInsideVertical
.LineStyle = xlContinuou
.Weight = xlThi
.ColorIndex = xlAutomati
End Wit
End Wit
End Su

problem referring to Range and Cells
 

With Worksheets("Sheet2")
With .Range(.Cells(3, 1), .Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End With

the unqualified cells refer to the activesheet. So qualify Range and both
Cells as above.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
The program halts and I get a Run-time error '1004' when I run the
following
code from Sheet1. But it works fine when I run it from Sheet2. Any
ideas?

Thanks.

Kevin

Sub test()
Dim Message As String
Message = MsgBox("This test # 1")
With Worksheets("Sheet2").Range(Cells(3, 1), Cells(3, 5))
Message = MsgBox("This test # 2")
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

problem referring to Range and Cells
 

Thanks Bob and Tom. This is good to know. That is, when using the With statement
qualify Worksheets and Ranges separately

I just now found the following in VB User Interface Help

Note Once a With block is entered, [the specific] object can't be changed
As a result, you can't use a single With statement to affect a number o
different objects

Thanks again

Kevin

problem referring to Range and Cells
 

That is correct, but an object can have a property, which is an object in
itself,. so you can have nested Withs , all referring to the top-level
object. This is what I used to circumvent the problem you had encountered
with

With Worksheets("Sheet2")
With .Range(.Cells(3, 1), .Cells(3, 5))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin" wrote in message
...
Thanks Bob and Tom. This is good to know. That is, when using the With
statement,
qualify Worksheets and Ranges separately.

I just now found the following in VB User Interface Help.

Note Once a With block is entered, [the specific] object can't be
changed.
As a result, you can't use a single With statement to affect a number
of
different objects.

Thanks again.

Kevin