problem referring to Range and Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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)
 
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
 
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.
 
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
 
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)
 
Back
Top