|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com