Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |