Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WITH .. END WITH question
Hi all,
Suppose originally I have the following code: Range("A1").CurrentRegion.Cells(1,1) = Range("A1").CurrentRegion.Cells(1,2) + 1 Range("A1").CurrentRegion.Cells(1,1).font.bold = True Now I want to simplify the two lines with WITH block: With Range("A1").CurrentRegion With .Cells(1,1) .Value = .Cells(1,2) + 1 'This line will fail .Font.Bold = True End With End With Of course it won't work. To circumvent the problem, I found I have to do this: With Range("A1").CurrentRegion With .Cells(1,1) .Value = Range("A1").CurrentRegion.Cells(1,2) + 1 'Troublesome workaround .Font.Bold = True End With End With However, I found my workaround really troublesome. Is there any simpler approach than my current troublesome workround? I found that the "Parent" keyword won't do the job. Thanks for your advice. Frederick Chow Hong Kong |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WITH .. END WITH question
I suspect that the following would work (untested).
With Range("A1").CurrentRegion .Cells(1,1).Value = .Cells(1,2) + 1 .Cells(1,1).Font.Bold = True End With When you first used the 2nd With...End With, the statement that failed was the equivalent of: With Range("A1").CurrentRegion .Cells(1,1).Value = .Cells(1,1).Cells(1,2) + 1 (etc) which, as you noted, will fail (deservedly so). When you use nested With..EndWiths your current level is your current level. There is no way to tell vba "use both With...EndWiths for the first part of this statement but only use the 1st With for the 2nd part", and that's pretty much what you were trying to do. Your choices are to 1) not use the 2nd With or use a fully qualified reference. Which is "simpler" is a matter of personal preference.... HTH, -- George Nicholson Remove 'Junk' from return address. "Frederick Chow" wrote in message ... Hi all, Suppose originally I have the following code: Range("A1").CurrentRegion.Cells(1,1) = Range("A1").CurrentRegion.Cells(1,2) + 1 Range("A1").CurrentRegion.Cells(1,1).font.bold = True Now I want to simplify the two lines with WITH block: With Range("A1").CurrentRegion With .Cells(1,1) .Value = .Cells(1,2) + 1 'This line will fail .Font.Bold = True End With End With Of course it won't work. To circumvent the problem, I found I have to do this: With Range("A1").CurrentRegion With .Cells(1,1) .Value = Range("A1").CurrentRegion.Cells(1,2) + 1 'Troublesome workaround .Font.Bold = True End With End With However, I found my workaround really troublesome. Is there any simpler approach than my current troublesome workround? I found that the "Parent" keyword won't do the job. Thanks for your advice. Frederick Chow Hong Kong |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks a lot (N/C)
"George Nicholson" wrote in message ... I suspect that the following would work (untested). With Range("A1").CurrentRegion .Cells(1,1).Value = .Cells(1,2) + 1 .Cells(1,1).Font.Bold = True End With When you first used the 2nd With...End With, the statement that failed was the equivalent of: With Range("A1").CurrentRegion .Cells(1,1).Value = .Cells(1,1).Cells(1,2) + 1 (etc) which, as you noted, will fail (deservedly so). When you use nested With..EndWiths your current level is your current level. There is no way to tell vba "use both With...EndWiths for the first part of this statement but only use the 1st With for the 2nd part", and that's pretty much what you were trying to do. Your choices are to 1) not use the 2nd With or use a fully qualified reference. Which is "simpler" is a matter of personal preference.... HTH, -- George Nicholson Remove 'Junk' from return address. "Frederick Chow" wrote in message ... Hi all, Suppose originally I have the following code: Range("A1").CurrentRegion.Cells(1,1) = Range("A1").CurrentRegion.Cells(1,2) + 1 Range("A1").CurrentRegion.Cells(1,1).font.bold = True Now I want to simplify the two lines with WITH block: With Range("A1").CurrentRegion With .Cells(1,1) .Value = .Cells(1,2) + 1 'This line will fail .Font.Bold = True End With End With Of course it won't work. To circumvent the problem, I found I have to do this: With Range("A1").CurrentRegion With .Cells(1,1) .Value = Range("A1").CurrentRegion.Cells(1,2) + 1 'Troublesome workaround .Font.Bold = True End With End With However, I found my workaround really troublesome. Is there any simpler approach than my current troublesome workround? I found that the "Parent" keyword won't do the job. Thanks for your advice. Frederick Chow Hong Kong |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WITH .. END WITH question
use
Dim rng as Range Set rng = Range("A1").CurrentRegion with rng etc. "Frederick Chow" wrote: Hi all, Suppose originally I have the following code: Range("A1").CurrentRegion.Cells(1,1) = Range("A1").CurrentRegion.Cells(1,2) + 1 Range("A1").CurrentRegion.Cells(1,1).font.bold = True Now I want to simplify the two lines with WITH block: With Range("A1").CurrentRegion With .Cells(1,1) .Value = .Cells(1,2) + 1 'This line will fail .Font.Bold = True End With End With Of course it won't work. To circumvent the problem, I found I have to do this: With Range("A1").CurrentRegion With .Cells(1,1) .Value = Range("A1").CurrentRegion.Cells(1,2) + 1 'Troublesome workaround .Font.Bold = True End With End With However, I found my workaround really troublesome. Is there any simpler approach than my current troublesome workround? I found that the "Parent" keyword won't do the job. Thanks for your advice. Frederick Chow Hong Kong |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|