I need help with 2 Questions

A

Aussiegirlone

::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?::

::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
 
S

Simon Lloyd

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

Try this

Code
-------------------
Sub NewRow(

Dim EndRowA As Lon
Dim NextRowAF As Lon
Dim wks As Workshee
Dim iRow As Lon
Dim i As Lon

For i = 6 To Sheets.Coun
With Sheets(i
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Ro
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row +
.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 = Tru
.Font.ColorIndex =
.Interior.ColorIndex = 3
.Borders.LineStyle = xlContinuou
.Borders.ColorIndex =
.Borders.Weight = xlThi
End Wit
End Wit
End Wit

For iRow = NextRowAF + 1 To 3
If Application.CountA(.Rows(iRow)) = 0 The
.Rows(iRow).Interior.ColorIndex =
End I
Next iRo

.Rows("5:32").RowHeight = 12.7
End Wit
Next
End Su
-------------------
In future when posting code please either click the # at the top of you
new post window and paste the code between the tags or highlight you
code and click the

Aussiegirlone;418773 said:
::Q1) An error message comes up when I run the code below, :
::Error message: “compile error, next without for” the highlight are
is (Next wks) right at the bottom of the code in-between (END WITH & EN
SUB) can someone fix it:
::Q2) Instead of the code below running on every sheet can someone mak
it skip the first 5 sheets using a sheet.count formula please?:
Code
-------------------
Option Explici
Sub NewRow(

Dim EndRowA As Lon
Dim NextRowAF As Lon
Dim wks As Workshee
Dim iRow As Lon

For Each wks In ActiveWorkbook.Worksheet
With wk
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Ro
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row +
.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 = Tru
.Font.ColorIndex =
.Interior.ColorIndex = 3
.Borders.LineStyle = xlContinuou
.Borders.ColorIndex =
.Borders.Weight = xlThi
End Wit

For iRow = NextRowAF + 1 To 3
If Application.CountA(.Rows(iRow)) = 0 The
.Rows(iRow).Interior.ColorIndex =
End I
Next iRo

.Rows("5:32").RowHeight = 12.7
End Wit
Next wk
End Su
-------------------
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
R

Rick Rothstein

For Question #1... I see 4 With statements but only 2 EndWith statements to
close them off. And about those With statements... why do you have 3 of them
in a row (the object of the last 2 With statements do not seem to reference
their predecessors)? Also, I'm not sure where they came from, but I see
double colons at the beginning and end of each code line... they make your
code very hard to read.
 
A

aussiegirlone

Now i get Compile error "End With end without"

Simon Lloyd said:
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 #





--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...
 
A

aussiegirlone

The original code is this! And it works beautiful as is!
But All I want is that this code runs by the sheet.count formula instead of
having to name a sheet or an array of sheets. Is that possible to do

Sub NewRow()
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "TotalHours"
Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")"
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2
Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AD"), Cells(n, "AC")).Interior.ColorIndex = 32
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.ColorIndex = 2
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AE"), Cells(n, "AC")).Interior.ColorIndex = 32
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.ColorIndex = 2
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AB"), Cells(n, "AC")).Interior.ColorIndex = 32
Union(Cells(n, "AA"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "A"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "B"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "C"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "D"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "E"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "F"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "G"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "H"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "I"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "J"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "K"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "L"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "M"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "N"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "O"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "P"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "Q"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "R"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "S"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "T"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "U"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "V"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "W"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "X"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "Y"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "Z"), Cells(n, "AB")).Interior.ColorIndex = 2


For i = n + 1 To 32
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Interior.ColorIndex = 2
End If
Next i
Range("A5").Select
Rows("5:32").Select
Selection.RowHeight = 12.75
Range("A5").Select

End Sub
 
R

Rick Rothstein

You appear to have a lot of redundant code. For example, this line of
code...
Union(Cells(n, "AB"), Cells(n, "AC")).Interior.ColorIndex = 32
Click to expand...

sets the interior color index of cell ABn to 32 and then the next 27 Union
statements continually reset it to a value of 2 (along with the companion
cell in the Union statement). If I read and interpret your code correctly,
then I *think* all of the code in the NewRow macro that you posted can be
replaced by this macro...

Sub NewRow()
Dim N As Long, I As Long
N = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(N, "AC").Value = "TotalHours"
With Cells(N, "AF")
.Formula = "=sum(AF5:AF" & N - 1 & ")"
.Font.Bold = True
.Font.ColorIndex = 2
End With
With Cells(N, "AC").Resize(, 4)
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
Cells(N, "A").Resize(, 28).Interior.ColorIndex = 2
For i = N + 1 To 32
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Interior.ColorIndex = 2
End If
Next i
Rows("5:32").RowHeight = 12.75
End Sub
 
S

Simon Lloyd

This should do what you want

Code
-------------------
Sub NewRow(
Dim i As Long, ic As Long, sc As Lon
Application.ScreenUpdating = Fals
For sc = 6 To Sheets.Coun
Sheets(sc).Selec
EndRow = Cells(Rows.Count, 1).End(xlUp).Ro
n = Cells(Rows.Count, "AF").End(xlUp).Row +
Cells(n, "AC").Value = "TotalHours
Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")
With Union(Cells(n, "AF"), Cells(n, "AC")
.Font.Bold = Tru
.Font.ColorIndex =
.Interior.ColorIndex = 3
.Borders.LineStyle = xlContinuou
.Borders.ColorIndex =
.Borders.Weight = xlThi
End Wit
With Union(Cells(n, "AD"), Cells(n, "AC")
.Interior.ColorIndex = 3
.Borders.LineStyle = xlContinuou
.Borders.ColorIndex =
.Borders.Weight = xlThi
End Wit
With Union(Cells(n, "AE"), Cells(n, "AC")
.Interior.ColorIndex = 3
.Borders.LineStyle = xlContinuou
.Borders.ColorIndex =
.Borders.Weight = xlThi
End Wit
For ic = 1 To 27 Step
Union(Cells(n, ic), Cells(n, "AB")).Interior.ColorIndex =
Next i
Union(Cells(n, "AB"), Cells(n, "AC")).Interior.ColorIndex = 3

For i = n + 1 To 3
If Application.CountA(Rows(i)) = 0 The
Rows(i).Interior.ColorIndex =
End I
Next
Range("A5").Selec
Rows("5:32").Selec
Selection.RowHeight = 12.7
Range("A5").Selec
Next s
Application.ScreenUpdating = Tru
End Su

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

aussiegirlone;418793 said:
The original code is this! And it works beautiful as is
But All I want is that this code runs by the sheet.count formul
instead o
having to name a sheet or an array of sheets. Is that possible to d
Code
-------------------
Sub NewRow(
EndRow = Cells(Rows.Count, 1).End(xlUp).Ro
n = Cells(Rows.Count, "AF").End(xlUp).Row +
Cells(n, "AC").Value = "TotalHours
Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = Tru
Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex =
Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 3
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuou
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex =
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThi
Union(Cells(n, "AD"), Cells(n, "AC")).Interior.ColorIndex = 3
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.LineStyle = xlContinuou
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.ColorIndex =
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.Weight = xlThi
Union(Cells(n, "AE"), Cells(n, "AC")).Interior.ColorIndex = 3
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.LineStyle = xlContinuou
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.ColorIndex =
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.Weight = xlThi
Union(Cells(n, "AB"), Cells(n, "AC")).Interior.ColorIndex = 3
Union(Cells(n, "AA"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "A"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "B"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "C"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "D"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "E"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "F"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "G"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "H"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "I"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "J"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "K"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "L"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "M"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "N"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "O"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "P"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "Q"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "R"), Cells(n, "AB")).Interior.ColorIndex =
Union(Cells(n, "S"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "T"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "U"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "V"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "W"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "X"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "Y"), Cells(n, "AB")).Interior.ColorIndex = 2
Union(Cells(n, "Z"), Cells(n, "AB")).Interior.ColorIndex = 2


For i = n + 1 To 32
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Interior.ColorIndex = 2
End If
Next i
Range("A5").Select
Rows("5:32").Select
Selection.RowHeight = 12.75
Range("A5").Select

End Sub
--------------------





Forums' (http://www.thecodecage.com/forumz/showthread.php?t=116530)
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
A

Aussiegirlone

Thankyou so much Simon, I did a small test of the code and it work
well; to everyone else that helped to resolve my issue thankyou ver
much and I luv you all
aussiegirlon
 
D

Dave Peterson

Check your other thread.
::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?::

::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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
Insert row on multiple sheets 6
Complicated formatting 2
Sheet Count & Insert NewRow 2
Insert New Row with auto formatting. 3
Save details 2
Removing duplicates 6
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0

Top