About Us

aussiegirlone

Now i get Compile error "End With end without"

"Simon Lloyd" wrote:

Hi Aussiegirlone, your problem was you didn't close off your with
statements properly:

Try this:

Code:
--------------------
Sub NewRow()

Dim EndRowA As Long
Dim NextRowAF As Long
Dim wks As Worksheet
Dim iRow As Long
Dim i As Long

For i = 6 To Sheets.Count
With Sheets(i)
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1
.Cells(NextRowAF, "AC").Value = "Total"
.Cells(NextRowAF, "AF").Formula _
= "=sum(AF5:AF" & NextRowAF - 1 & ")"
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AD"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AE"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
End With
End With

For iRow = NextRowAF + 1 To 32
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Interior.ColorIndex = 2
End If
Next iRow

.Rows("5:32").RowHeight = 12.75
End With
Next i
End Sub
--------------------
In future when posting code please either click the # at the top of your
new post window and paste the code between the tags or highlight your
code and click the #

Aussiegirlone;418773 Wrote:
::Q1) An error message comes up when I run the code below, ::
::Error message: €œcompile error, next without for€ the highlight area
is (Next wks) right at the bottom of the code in-between (END WITH & END
SUB) can someone fix it::
::Q2) Instead of the code below running on every sheet can someone make
it skip the first 5 sheets using a sheet.count formula please?::

Code:
--------------------

Option Explicit
Sub NewRow()

Dim EndRowA As Long
Dim NextRowAF As Long
Dim wks As Worksheet
Dim iRow As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1
.Cells(NextRowAF, "AC").Value = "Total"
.Cells(NextRowAF, "AF").Formula _
= "=sum(AF5:AF" & NextRowAF - 1 & ")"
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AD"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AE"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With

For iRow = NextRowAF + 1 To 32
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Interior.ColorIndex = 2
End If
Next iRow

.Rows("5:32").RowHeight = 12.75
End With
Next wks
End Sub

--------------------

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
2 questions.	CelticCharmer	Excel Discussion (Misc queries)	4	November 2nd 08 04:33 PM
View Questions and Answer to questions I created	Roibn Taylor	Excel Discussion (Misc queries)	4	July 24th 08 12:05 AM
3 Questions	Django Cat[_2_]	Excel Discussion (Misc queries)	16	November 12th 07 10:30 PM
2 Questions	Rich Stanek	Excel Worksheet Functions	1	June 7th 07 09:43 AM
2 questions	Sally Sibthorpe	Charts and Charting in Excel	9	July 26th 06 10:15 PM

Menu

About Us