Why Doesn't This Nested "with" work?
 

Why do I get an "End with without With" error on this? Assume that all the
other work is correct in this snippet, but I get the error on the final end
with. This is in a UserForm procedure when the user clicks an OK button, if
that matters. According to the Help, you can nest with commands.....


With rngCoOwners
.Sort Key1:="Last Name", Order1:=xlAscending, Key2:="First
Name",_ Order2:=xlAscending, Header:=xlYes
For intCounter = 1 To .Rows.Count
If .Cells(intCounter, 12) = "Yes" Then
Set wkshtCoOwnerStatement =
ActiveWorkbook.Worksheets(.Cells_(intCounter, 2) & ", " &
Left(.Cells(intCounter, 3), 1))
Set rngStatementHeader =
wkshtCoOwnerStatement.Range_("tblStatementHeader")
With rngStatementHeader
.Cells(rngStatementHeader.Rows.Count, 1).Value =
"Send Check_ Payable to: Happy Hollow RV Resort, " & Me.txtboxReturnAddress
End With
End If 'check to see if they are current
End With

Why Doesn't This Nested "with" work?
 

Ignore the _ characters in there, I put them in based on the wordwrap of the
message positing page, then it formatted them differently when it posted to
the site.....

"Steve Haack" wrote:

Why do I get an "End with without With" error on this? Assume that all the
other work is correct in this snippet, but I get the error on the final end
with. This is in a UserForm procedure when the user clicks an OK button, if
that matters. According to the Help, you can nest with commands.....


With rngCoOwners
.Sort Key1:="Last Name", Order1:=xlAscending, Key2:="First
Name",_ Order2:=xlAscending, Header:=xlYes
For intCounter = 1 To .Rows.Count
If .Cells(intCounter, 12) = "Yes" Then
Set wkshtCoOwnerStatement =
ActiveWorkbook.Worksheets(.Cells_(intCounter, 2) & ", " &
Left(.Cells(intCounter, 3), 1))
Set rngStatementHeader =
wkshtCoOwnerStatement.Range_("tblStatementHeader")
With rngStatementHeader
.Cells(rngStatementHeader.Rows.Count, 1).Value =
"Send Check_ Payable to: Happy Hollow RV Resort, " & Me.txtboxReturnAddress
End With
End If 'check to see if they are current
End With

Why Doesn't This Nested "with" work?
 

Necer mind, I figured it out. I forgot the next to go with the counter loop.

"Steve Haack" wrote:

Ignore the _ characters in there, I put them in based on the wordwrap of the
message positing page, then it formatted them differently when it posted to
the site.....

"Steve Haack" wrote:

Why do I get an "End with without With" error on this? Assume that all the
other work is correct in this snippet, but I get the error on the final end
with. This is in a UserForm procedure when the user clicks an OK button, if
that matters. According to the Help, you can nest with commands.....


With rngCoOwners
.Sort Key1:="Last Name", Order1:=xlAscending, Key2:="First
Name",_ Order2:=xlAscending, Header:=xlYes
For intCounter = 1 To .Rows.Count
If .Cells(intCounter, 12) = "Yes" Then
Set wkshtCoOwnerStatement =
ActiveWorkbook.Worksheets(.Cells_(intCounter, 2) & ", " &
Left(.Cells(intCounter, 3), 1))
Set rngStatementHeader =
wkshtCoOwnerStatement.Range_("tblStatementHeader")
With rngStatementHeader
.Cells(rngStatementHeader.Rows.Count, 1).Value =
"Send Check_ Payable to: Happy Hollow RV Resort, " & Me.txtboxReturnAddress
End With
End If 'check to see if they are current
End With

Why Doesn't This Nested "with" work?
 

I'm glad to see that I am not the only one that posts a question then shortly
after that, not being able to give it a rest, figure out the answer :)
--
Regards,
John


"Steve Haack" wrote:

Necer mind, I figured it out. I forgot the next to go with the counter loop.

"Steve Haack" wrote:

Ignore the _ characters in there, I put them in based on the wordwrap of the
message positing page, then it formatted them differently when it posted to
the site.....

"Steve Haack" wrote:

Why do I get an "End with without With" error on this? Assume that all the
other work is correct in this snippet, but I get the error on the final end
with. This is in a UserForm procedure when the user clicks an OK button, if
that matters. According to the Help, you can nest with commands.....


With rngCoOwners
.Sort Key1:="Last Name", Order1:=xlAscending, Key2:="First
Name",_ Order2:=xlAscending, Header:=xlYes
For intCounter = 1 To .Rows.Count
If .Cells(intCounter, 12) = "Yes" Then
Set wkshtCoOwnerStatement =
ActiveWorkbook.Worksheets(.Cells_(intCounter, 2) & ", " &
Left(.Cells(intCounter, 3), 1))
Set rngStatementHeader =
wkshtCoOwnerStatement.Range_("tblStatementHeader")
With rngStatementHeader
.Cells(rngStatementHeader.Rows.Count, 1).Value =
"Send Check_ Payable to: Happy Hollow RV Resort, " & Me.txtboxReturnAddress
End With
End If 'check to see if they are current
End With