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

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" <PleaseRemoveThishkmusc@yahoo.com> wrote in message
news:e$vwwpQTGHA.2816@TK2MSFTNGP10.phx.gbl...
> 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
>

"George Nicholson" <JunkGeorgeN@msn.com> wrote in message
news:%23vnVGHRTGHA.4956@TK2MSFTNGP09.phx.gbl...
>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" <PleaseRemoveThishkmusc@yahoo.com> wrote in message
> news:e$vwwpQTGHA.2816@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>
>

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