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

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

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

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

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

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

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