| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro won't find the last row !
I am reposting this as I cannot get a reply to my problem. The following
macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line ..Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
You have not assigned a value to the l4 variable.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Roger" wrote in message ... I am reposting this as I cannot get a reply to my problem. The following macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
Hi Roger,
In addition to Chip's response Change: . Range("AF" & lr).").borders.LineStyle = xlContinuous to .Range("AF" & lr).Borders.LineStyle = xlContinuous Additionally, the line . Range("H" & lr).formula="=sum(h1:h" & lr & ")" will create a crrcular reference. Try, instead: ..Range("H" & lr).Formula = "=sum(h1:h" & lr - 1 & ")" Incrporating these change, try: --- Regards, Norman |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
It was anyone's guess since the variables were not declared
(shame, shame, shame). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Don Guillett" wrote in message ... It probably should have been LR. Typo. Probably mine when I hurriedly provided the code originally. -- Don Guillett SalesAid Software "Chip Pearson" wrote in message ... You have not assigned a value to the l4 variable. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Roger" wrote in message ... I am reposting this as I cannot get a reply to my problem. The following macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
You might want to double check your cell references
with workSheets("Report (zonderArb)") ....... ...... .Range(cells(lr,"h"),cells(lr,"o")).borders.LineSt yle = xlContinuous End With If this worksheet is not the activesheet cells(lr,"h") refers the the cell on the active sheet, not "Report (zonderArg)". Cells should also be preceded by a period with workSheets("Report (zonderArb)") ....... ...... .Range(.cells(lr,"h"), .cells(lr,"o")).borders.LineStyle = xlContinuous End With "Roger" wrote: I am reposting this as I cannot get a reply to my problem. The following macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line ..Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
As an amateur to a group of professionals, thanks for the advice but you
have now lost me completely !! What is the problem with the code below, sure it is not just making lr into LR ?? ... Roger "Don Guillett" wrote in message ... It probably should have been LR. Typo. Probably mine when I hurriedly provided the code originally. -- Don Guillett SalesAid Software "Chip Pearson" wrote in message ... You have not assigned a value to the l4 variable. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Roger" wrote in message ... I am reposting this as I cannot get a reply to my problem. The following macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
This is the code that I am using. When I run it still get an error on the
line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just does not want to count the rows to determine which row is the last row in the table of data ! thanks ... Roger With Worksheets("Report (zonderArb)") ..LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1 ..Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")" ..Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae")) ..Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous ..Range("AF" & LR).Borders.LineStyle = xlContinuous ..Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00" ..Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00" ..Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00" ..Range("G" & LR) = "Totals" With Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With End With 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
I know, I know, I know
I should do that -- Don Guillett SalesAid Software "Chip Pearson" wrote in message ... It was anyone's guess since the variables were not declared (shame, shame, shame). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Don Guillett" wrote in message ... It probably should have been LR. Typo. Probably mine when I hurriedly provided the code originally. -- Don Guillett SalesAid Software "Chip Pearson" wrote in message ... You have not assigned a value to the l4 variable. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Roger" wrote in message ... I am reposting this as I cannot get a reply to my problem. The following macro is meant to go to the last row and then on the next row (lr+1) make a totals row. But when I run the macro I get an error message "application-defined or object-defined error" and it does to the line .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous and after as the problem code. Can someone help me please ? Sub MakeTotals()' ' Make Totals for Report (zonderArb) Macro ' Macro recorded 15/06/02 by RAO ' Keyboard Shortcut: Ctrl+Shift+T ' clears borders from rows below total line (row 460) for clean printing ' creates SUM formula and then copies across total line ' creates borders and formats 0.00 where appropriate ' saves worksheet lr=cells(rows.count,"H").end(xlup).row+1 Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone with workSheets("Report (zonderArb)") . Range("H" & lr).formula="=sum(h1:h" & lr & ")" . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae")) . Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous . Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous . Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous . Range("AF" & lr).").borders.LineStyle = xlContinuous . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00" . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00" . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00" . Range("G" & LR)= "Totals" With . Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With end with 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
A lot of this is my fault for being in a hurry when I tried to help clean up
your code. This idea here HAS been tested I am finding the last row on sheet3 which is NOT the active sheet. As was pointed out, notice the .'s in the with. I am copying range("a4:d" & LR) to the active sheet. Either line works. Sub dolastrowexercise() With Sheets("sheet3") LR = .Cells(Rows.Count, "H").End(xlUp).Row MsgBox LR '.Range(.Cells(4, 1), .Cells(LR, 4)).Copy Range("a15") ..Range("a4:d" & LR).Copy Range("a15")'with .'s '.Range("a4:d" & LR).Copy sheets("sheet5").Range("a15")'no dots End With End Sub -- Don Guillett SalesAid Software "Roger" wrote in message ... This is the code that I am using. When I run it still get an error on the line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just does not want to count the rows to determine which row is the last row in the table of data ! thanks ... Roger With Worksheets("Report (zonderArb)") .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1 .Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")" .Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae")) .Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous .Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous .Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous .Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous .Range("AF" & LR).Borders.LineStyle = xlContinuous .Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00" .Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00" .Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00" .Range("G" & LR) = "Totals" With Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With End With 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My macro won't find the last row !
LR should not be preceded by a period. Also, do you have any chart sheets in
your workbook? If so I would change Rows.Count to .Rows.Count Rows.Count will refer to the active sheet, which will fail if it happens to be a chart. "Roger" wrote: This is the code that I am using. When I run it still get an error on the line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just does not want to count the rows to determine which row is the last row in the table of data ! thanks ... Roger With Worksheets("Report (zonderArb)") ..LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1 ..Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")" ..Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae")) ..Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous ..Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous ..Range("AF" & LR).Borders.LineStyle = xlContinuous ..Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00" ..Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00" ..Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00" ..Range("G" & LR) = "Totals" With Range("G" & LR).Characters(Start:=1, Length:=7).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 1 End With End With 'Sheets("Instructions").Select ' Range("K61").Select ActiveWorkbook.Save End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get Macro warning, but can't find Macro | Excel Worksheet Functions | |||
| Find Macro | Excel Discussion (Misc queries) | |||
| change error message when no more for "find" in macro to find | Excel Programming | |||
| cant find a macro | Excel Programming | |||
| I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
![Roger[_22_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
