Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to first cell in a range | Excel Discussion (Misc queries) | |||
Formula referring to a dynamic range in a different workbook | Excel Worksheet Functions | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
Selecting a range without referring to specific cells | Excel Programming | |||
Referring to a user-selected range | Excel Programming |