With statement
 

Is it possible to have nested With statements ??

Problem I have - VBA application that refers back and forth between
workbook / worksheets. I have an 'exterior' With statement like this...

With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

Workbooks(AnotherWorkbook).Sheets(3).Activate
With Workbooks(AnotherWorkbook).Sheets(3)

' interior code here will make references to inner With
.Range("someRange").Value = ...

End With
End With

I want to refer to a range defined in the outer With ...is that possible
(and how ?)

Appreciate any advice.

Thanks,
Chad

With statement
 

You can, but the object within the secondary With must be part of the
primary with object, or totally independent. You cannot refer to both with
objects from within either structure using the dot notation, one would have
to be fully qualified.

So you cannot do what you are trying to do.
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ChadF" wrote in message
...
Is it possible to have nested With statements ??

Problem I have - VBA application that refers back and forth between
workbook / worksheets. I have an 'exterior' With statement like this...

With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

Workbooks(AnotherWorkbook).Sheets(3).Activate
With Workbooks(AnotherWorkbook).Sheets(3)

' interior code here will make references to inner With
.Range("someRange").Value = ...

End With
End With

I want to refer to a range defined in the outer With ...is that possible
(and how ?)

Appreciate any advice.

Thanks,
Chad

With statement
 

Thanks Bob, that's pretty much what I figured. In the example I provided, I
wound up simply defining a worksheet variable at the top of my subroutine and
referenced it that way ... reads like :

Dim aWS as Worksheet
....

Set aWS = Workbooks(AnotherWorkbook).Sheets(3)
With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

aWS.Activate
aWS.Range("B1:B10").Value = .Range("someRange").Value

' (and so forth)

End With

Appreciate your help.
Chad


"Bob Phillips" wrote:

You can, but the object within the secondary With must be part of the
primary with object, or totally independent. You cannot refer to both with
objects from within either structure using the dot notation, one would have
to be fully qualified.

So you cannot do what you are trying to do.
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ChadF" wrote in message
...
Is it possible to have nested With statements ??

Problem I have - VBA application that refers back and forth between
workbook / worksheets. I have an 'exterior' With statement like this...

With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

Workbooks(AnotherWorkbook).Sheets(3).Activate
With Workbooks(AnotherWorkbook).Sheets(3)

' interior code here will make references to inner With
.Range("someRange").Value = ...

End With
End With

I want to refer to a range defined in the outer With ...is that possible
(and how ?)

Appreciate any advice.

Thanks,
Chad

With statement
 

That's how I do it also.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ChadF" wrote in message
...
Thanks Bob, that's pretty much what I figured. In the example I provided,
I
wound up simply defining a worksheet variable at the top of my subroutine
and
referenced it that way ... reads like :

Dim aWS as Worksheet
...

Set aWS = Workbooks(AnotherWorkbook).Sheets(3)
With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

aWS.Activate
aWS.Range("B1:B10").Value = .Range("someRange").Value

' (and so forth)

End With

Appreciate your help.
Chad


"Bob Phillips" wrote:

You can, but the object within the secondary With must be part of the
primary with object, or totally independent. You cannot refer to both
with
objects from within either structure using the dot notation, one would
have
to be fully qualified.

So you cannot do what you are trying to do.
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ChadF" wrote in message
...
Is it possible to have nested With statements ??

Problem I have - VBA application that refers back and forth between
workbook / worksheets. I have an 'exterior' With statement like
this...

With Workbooks(ThisworkBook).Sheets(MySheet.Name)
' (some code)

Workbooks(AnotherWorkbook).Sheets(3).Activate
With Workbooks(AnotherWorkbook).Sheets(3)

' interior code here will make references to inner With
.Range("someRange").Value = ...

End With
End With

I want to refer to a range defined in the outer With ...is that
possible
(and how ?)

Appreciate any advice.

Thanks,
Chad