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

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

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

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

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

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

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

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

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

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

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