WITH .. END WITH question

[Frederick Chow]

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]

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,

 

[Frederick Chow]

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,

 

[Duke Carey]

use

Dim rng as Range
Set rng = Range("A1").CurrentRegion

with rng
etc.