Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > I need help with 2 Questions
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default I need help with 2 Questions

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


--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default I need help with 2 Questions

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default I need help with 2 Questions
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.

--
Rick (MVP - Excel)


"Aussiegirlone" wrote in message
...

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


--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile:
http://www.thecodecage.com/forumz/member.php?userid=272
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=116530


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 94
Default I need help with 2 Questions
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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 94
Default I need help with 2 Questions
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





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

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


--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default I need help with 2 Questions
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

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

--
Rick (MVP - Excel)


"aussiegirlone" wrote in message
...
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





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

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


--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile:
http://www.thecodecage.com/forumz/member.php?userid=272
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=116530



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default I need help with 2 Questions

This should do what you want:


Code:
--------------------
Sub NewRow()
Dim i As Long, ic As Long, sc As Long
Application.ScreenUpdating = False
For sc = 6 To Sheets.Count
Sheets(sc).Select
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 & ")"
With Union(Cells(n, "AF"), Cells(n, "AC"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
With Union(Cells(n, "AD"), Cells(n, "AC"))
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
With Union(Cells(n, "AE"), Cells(n, "AC"))
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
For ic = 1 To 27 Step 1
Union(Cells(n, ic), Cells(n, "AB")).Interior.ColorIndex = 2
Next ic
Union(Cells(n, "AB"), Cells(n, "AC")).Interior.ColorIndex = 32

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
Next sc
Application.ScreenUpdating = True
End Sub

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


aussiegirlone;418793 Wrote:
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


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

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

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





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

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


--
Aussiegirlone

------------------------------------------------------------------------
Aussiegirlone's Profile: 'The Code Cage Forums - View Profile:
Aussiegirlone'
(http://www.thecodecage.com/forumz/member.php?userid=272)
View this thread: 'I need help with 2 Questions - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=116530)




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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default I need help with 2 Questions

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


--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default I need help with 2 Questions
Check your other thread.

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

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

--
Aussiegirlone
------------------------------------------------------------------------
Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

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


All times are GMT +1. The time now is 05:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement