Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi,
Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Yep.
It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi Dave,
Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
You are missing the real benefit of being able to nest With blocks. Study
this off-the-top-of-my-head example to see how nesting can be useful. Go to Sheet1, put some entries in Column A (they don't have to be contiguous and any old text will do for example purposes) and then run this macro... Sub Test() Dim X As Long Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastRow If .Cells(X, "A").Value < "" Then With .Cells(X, "A") With .Font .Size = 18 .Bold = True .Italic = True End With .ColumnWidth = 10 + .ColumnWidth With .Borders .ColorIndex = 3 .LineStyle = xlDouble End With End With End If Next End With End Sub -- Rick (MVP - Excel) "Jac Tremblay" wrote in message ... Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
It's not the nesting that's the problem. It's that excel/VBE won't use the
intellisense with "with worksheets(...)" Try: Dim wks as worksheet set wks = ActiveWorkbook.Sheets("Sheet1") .... With wks set rngrange = . and you'll see the intellisense. If you declare your objects correctly (as Worksheet, as range, ... and not As Variant, As Object), you'll see that helpful intellisense. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Just to add...
I'll often use: dim ActWks as worksheet set ActWks = activesheet with actwks .range(... Just so that I get the intellisense help. And I won't rely on unqualified ranges being on the activesheet (ok for code in a general module), but can cause trouble if you copy|paste code elsewhere. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi Rick,
What a great example. Thank you very much. Have a good day. -- Jac Tremblay "Rick Rothstein" wrote: You are missing the real benefit of being able to nest With blocks. Study this off-the-top-of-my-head example to see how nesting can be useful. Go to Sheet1, put some entries in Column A (they don't have to be contiguous and any old text will do for example purposes) and then run this macro... Sub Test() Dim X As Long Dim LastRow As Long With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastRow If .Cells(X, "A").Value < "" Then With .Cells(X, "A") With .Font .Size = 18 .Bold = True .Italic = True End With .ColumnWidth = 10 + .ColumnWidth With .Borders .ColorIndex = 3 .LineStyle = xlDouble End With End With End If Next End With End Sub -- Rick (MVP - Excel) "Jac Tremblay" wrote in message ... Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi Dave,
You are right. That is what I always do normally except for short examples or tests. By the way, is it any better to declare a Workbook object like this: Dim Wbk as Excel.Workbook or like this: Dim Wbk as Workbook? Is there any difference? Thank you again and have a good week end. -- Jac Tremblay "Dave Peterson" wrote: It's not the nesting that's the problem. It's that excel/VBE won't use the intellisense with "with worksheets(...)" Try: Dim wks as worksheet set wks = ActiveWorkbook.Sheets("Sheet1") .... With wks set rngrange = . and you'll see the intellisense. If you declare your objects correctly (as Worksheet, as range, ... and not As Variant, As Object), you'll see that helpful intellisense. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi again.
Thanks again. -- Jac Tremblay "Dave Peterson" wrote: Just to add... I'll often use: dim ActWks as worksheet set ActWks = activesheet with actwks .range(... Just so that I get the intellisense help. And I won't rely on unqualified ranges being on the activesheet (ok for code in a general module), but can cause trouble if you copy|paste code elsewhere. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
If I'm in Excel, I use:
dim wkb as workbook I've read some posts from very smart people who always qualify every object. I think that their reasoning is that it makes it easier when they copy the code to a different application (like automating excel from word/access/powerpoint). I'm not as careful as I should be! Jac Tremblay wrote: Hi Dave, You are right. That is what I always do normally except for short examples or tests. By the way, is it any better to declare a Workbook object like this: Dim Wbk as Excel.Workbook or like this: Dim Wbk as Workbook? Is there any difference? Thank you again and have a good week end. -- Jac Tremblay "Dave Peterson" wrote: It's not the nesting that's the problem. It's that excel/VBE won't use the intellisense with "with worksheets(...)" Try: Dim wks as worksheet set wks = ActiveWorkbook.Sheets("Sheet1") .... With wks set rngrange = . and you'll see the intellisense. If you declare your objects correctly (as Worksheet, as range, ... and not As Variant, As Object), you'll see that helpful intellisense. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested With
Hi again Dave,
That is a good comment. I am trying to set standards for developpers where I work and that is not always easy. One has to be aware of the environment. For example, in VB.NET when I do automation, I declare a range differently for Word and Excel I use wdRng as a prefix for Word ranges and xlRng for Excel ranges. It helps me a lot when reading my own code and developping new applications. Thanks again for your time. -- Jac Tremblay "Dave Peterson" wrote: If I'm in Excel, I use: dim wkb as workbook I've read some posts from very smart people who always qualify every object. I think that their reasoning is that it makes it easier when they copy the code to a different application (like automating excel from word/access/powerpoint). I'm not as careful as I should be! Jac Tremblay wrote: Hi Dave, You are right. That is what I always do normally except for short examples or tests. By the way, is it any better to declare a Workbook object like this: Dim Wbk as Excel.Workbook or like this: Dim Wbk as Workbook? Is there any difference? Thank you again and have a good week end. -- Jac Tremblay "Dave Peterson" wrote: It's not the nesting that's the problem. It's that excel/VBE won't use the intellisense with "with worksheets(...)" Try: Dim wks as worksheet set wks = ActiveWorkbook.Sheets("Sheet1") .... With wks set rngrange = . and you'll see the intellisense. If you declare your objects correctly (as Worksheet, as range, ... and not As Variant, As Object), you'll see that helpful intellisense. Jac Tremblay wrote: Hi Dave, Your answer was quick. My code was only an example that I built on the fly. Here is a new test I just made up. Dim rngRange As Range Dim rngCell As Range Dim intI As Integer With ActiveWorkbook.Sheets("Sheet1") Set rngRange = .Range(.Range("A1").Offset(1, 0), _ .Range("A1").Offset(1, 0).End(xlDown)) For Each rngCell In rngRange With ActiveWorkbook.Sheets("Sheet2").Range("C3") .Offset(intI, 0).Value = rngCell.Value intI = intI + 1 End With Next rngCell Set rngRange = Nothing End With It works fine. I know that here too I could have gone without nesting the With statements but that is not the point. The reason I posted this question is that when you type a dot after an object name in the inner With, nothing appears as if there was something wrong. I guess that Excel cannot guess which object you are refering to. Thank you Dave for your comment. -- Jac Tremblay "Dave Peterson" wrote: Yep. It worked when you tried it, right? <vbg But you could have gotten away without nesting, too: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) End with For Each rngCell In rng1stCol.Cells With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing Jac Tremblay wrote: Hi, Is it possible to nest With... End With statements? Here is an example: With ActiveWorkbook.Sheets("Param") ' ... Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _ .Range(strRange).Offset(2, 0).End(xlDown)) For Each rngCell In rng1stCol With Me.lstTest .AddItem rngCell.Value .List(.ListCount - 1, 1) = _ Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd") End With Next rngCell Set rng1stCol = Nothing End With I just need some confirmation. Thanks. -- Jac Tremblay -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested if | Excel Discussion (Misc queries) | |||
Nested IF? | Excel Worksheet Functions | |||
nested ifs not enough | Excel Discussion (Misc queries) | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
What is quicker? Nested or non nested ifs | Excel Programming |