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