Can I nest an 'If' statement in a 'With' statement?
 

Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get the
Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

For what you posted, you are missing the End If statement for your
If..Then..Else statements.

With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
' You Else condition statements would go here, if any
End If
End With

--
Rick (MVP - Excel)


"DK" wrote in message
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

Sub Clear_Entries()
Dim fr As Long, lr As Long, fc As Long, lc As Long
Dim nRows As Long

fr = Range("MtrHeader").Row + 1
With Sheet1
nRows = .Rows.Count
fr = Range("MtrHeader").Row + 1
lr = .Range("A" & nRows).End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else

End If
End With
End Sub

Note change of Integer to As Long and nRows for 65536 (ie for xl2007 and
earlier). I didn't test it though.

Regards,
Peter T

"DK" wrote in message
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

So elementary :-|
Thanks Rick
DK

"Rick Rothstein" wrote in message
...
For what you posted, you are missing the End If statement for your
If..Then..Else statements.

With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
' You Else condition statements would go here, if any
End If
End With

--
Rick (MVP - Excel)


"DK" wrote in message
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

Hi

You miss an End If statement before End With.

With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
End If
End With

Regards,
Per

"DK" skrev i meddelelsen
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

Thank you Peter for your prompt reply.
DK

"Peter T" <peter_t@discussions wrote in message
...
Sub Clear_Entries()
Dim fr As Long, lr As Long, fc As Long, lc As Long
Dim nRows As Long

fr = Range("MtrHeader").Row + 1
With Sheet1
nRows = .Rows.Count
fr = Range("MtrHeader").Row + 1
lr = .Range("A" & nRows).End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else

End If
End With
End Sub

Note change of Integer to As Long and nRows for 65536 (ie for xl2007 and
earlier). I didn't test it though.

Regards,
Peter T

"DK" wrote in message
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

Can I nest an 'If' statement in a 'With' statement?
 

Thank you for the prompt reply. Much appreciated.
DK

"Per Jessen" wrote in message
...
Hi

You miss an End If statement before End With.

With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
End If
End With

Regards,
Per

"DK" skrev i meddelelsen
...
Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get
the Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr = fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub