Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
I am looking for help with a macro that will enter in every cell in a
workbook the contents I am a winner so that every row in every column is filled with these contents until Excel cannot create any more worksheets to store these contents and then at that point the macro will stop. Can you help me with that? Here is what I am starting with. Sub Macro1() Range("A1: ").Select ActiveCell.FormulaR1C1 = "I am a winner" Range("A2").Select A3:IV65535 then ActiveCell.FormulaR1C1 = "I am a winner" Range("IV65536").Select ActiveCell.FormulaR1C1 = "I am a winner" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
I'm not sure that I even want to know why you want to do this, but
assuming you have your reasons, try something like: Sub WhyInTheWorldDoYouWantToDoThis() Dim WS As Excel.Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With On Error GoTo ErrH: ' fill the existing sheets For Each WS In ThisWorkbook.Worksheets WS.Cells.Value = "I am a winner" Next WS ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop ErrH: With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub You'll blow up with a "not enough resources" error somewhere along the way. Remember, XL2007 has 17 BILLION cells PER WORKSHEET so the workbook that results will be VERY large. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 22 Jul 2009 16:34:01 -0700, Louie wrote: I am looking for help with a macro that will enter in every cell in a workbook the contents I am a winner so that every row in every column is filled with these contents until Excel cannot create any more worksheets to store these contents and then at that point the macro will stop. Can you help me with that? Here is what I am starting with. Sub Macro1() Range("A1: ").Select ActiveCell.FormulaR1C1 = "I am a winner" Range("A2").Select A3:IV65535 then ActiveCell.FormulaR1C1 = "I am a winner" Range("IV65536").Select ActiveCell.FormulaR1C1 = "I am a winner" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
I definitely don't want to know why the OP wants to do this.<g Given that,
would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... I'm not sure that I even want to know why you want to do this, but assuming you have your reasons, try something like: Sub WhyInTheWorldDoYouWantToDoThis() Dim WS As Excel.Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With On Error GoTo ErrH: ' fill the existing sheets For Each WS In ThisWorkbook.Worksheets WS.Cells.Value = "I am a winner" Next WS ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop ErrH: With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub You'll blow up with a "not enough resources" error somewhere along the way. Remember, XL2007 has 17 BILLION cells PER WORKSHEET so the workbook that results will be VERY large. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 22 Jul 2009 16:34:01 -0700, Louie wrote: I am looking for help with a macro that will enter in every cell in a workbook the contents "I am a winner" so that every row in every column is filled with these contents until Excel cannot create any more worksheets to store these contents and then at that point the macro will stop. Can you help me with that? Here is what I am starting with. Sub Macro1() Range("A1: ").Select ActiveCell.FormulaR1C1 = "I am a winner" Range("A2").Select A3:IV65535 then ActiveCell.FormulaR1C1 = "I am a winner" Range("IV65536").Select ActiveCell.FormulaR1C1 = "I am a winner" End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
Rick,
Yeah, your approach is probably better, but given the context, I can't imagine that it really matters much. Like you, I didn't bother to test my code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 22 Jul 2009 20:47:26 -0400, "Rick Rothstein" wrote: I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
Not only don't want to know why, don't want to know the outcome!:(
"Rick Rothstein" wrote in message ... I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... I'm not sure that I even want to know why you want to do this, but assuming you have your reasons, try something like: Sub WhyInTheWorldDoYouWantToDoThis() Dim WS As Excel.Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With On Error GoTo ErrH: ' fill the existing sheets For Each WS In ThisWorkbook.Worksheets WS.Cells.Value = "I am a winner" Next WS ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(after:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop ErrH: With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub You'll blow up with a "not enough resources" error somewhere along the way. Remember, XL2007 has 17 BILLION cells PER WORKSHEET so the workbook that results will be VERY large. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 22 Jul 2009 16:34:01 -0700, Louie wrote: I am looking for help with a macro that will enter in every cell in a workbook the contents "I am a winner" so that every row in every column is filled with these contents until Excel cannot create any more worksheets to store these contents and then at that point the macro will stop. Can you help me with that? Here is what I am starting with. Sub Macro1() Range("A1: ").Select ActiveCell.FormulaR1C1 = "I am a winner" Range("A2").Select A3:IV65535 then ActiveCell.FormulaR1C1 = "I am a winner" Range("IV65536").Select ActiveCell.FormulaR1C1 = "I am a winner" End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
Ok, but I would like to know the file size. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
Thank you guys for your willingness to help out. I know the macro I am
asking for help with is a little different. For what its worth I think this is really great and its very useful to me. However your comments are very funny and have been making me laugh very hard. Thank you for the many chuckles. I am wondering is there a way to set up this macro so that I can see the contents being entered in every cell one by one and the columns will autofit to the contents from the beginning? "Jim Cone" wrote: Ok, but I would like to know the file size. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
That is not a practical request. Consider this... there are 16,777,216 cells
on a single worksheet. I don't know how fast they could fill in; but, for the sake of argument, assume 100 every second (that's a rate you would have trouble being able to follow cell-by-cell). Since there are 86,400 seconds in a day, it would take a little more than 1.94 DAYS to watch that one, SINGLE worksheet fill in... and that calculation is without breaks for bathroom, eating or sleeping! You couldn't possibly watch an entire workbook of these worksheets fill up until Excel finally "blew up". -- Rick (MVP - Excel) "Louie" wrote in message ... Thank you guys for your willingness to help out. I know the macro I am asking for help with is a little different. For what its worth I think this is really great and its very useful to me. However your comments are very funny and have been making me laugh very hard. Thank you for the many chuckles. I am wondering is there a way to set up this macro so that I can see the contents being entered in every cell one by one and the columns will autofit to the contents from the beginning? "Jim Cone" wrote: Ok, but I would like to know the file size. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
I don't know, Rick, I'm thinking it will start with the bells and whistles
within the first one or two minutes unless the OP has some giga giga bytes of memory. I have 512 mb and I don't think that would last too long in xl2003. "Rick Rothstein" wrote in message ... That is not a practical request. Consider this... there are 16,777,216 cells on a single worksheet. I don't know how fast they could fill in; but, for the sake of argument, assume 100 every second (that's a rate you would have trouble being able to follow cell-by-cell). Since there are 86,400 seconds in a day, it would take a little more than 1.94 DAYS to watch that one, SINGLE worksheet fill in... and that calculation is without breaks for bathroom, eating or sleeping! You couldn't possibly watch an entire workbook of these worksheets fill up until Excel finally "blew up". -- Rick (MVP - Excel) "Louie" wrote in message ... Thank you guys for your willingness to help out. I know the macro I am asking for help with is a little different. For what its worth I think this is really great and it's very useful to me. However your comments are very funny and have been making me laugh very hard. Thank you for the many chuckles. I am wondering is there a way to set up this macro so that I can see the contents being entered in every cell one by one and the columns will autofit to the contents from the beginning? "Jim Cone" wrote: Ok, but I would like to know the file size. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
You may be right but, again, I'm unwilling to test it. I just figured the
interface between VB and the worksheet tends to be slow and doing a one-by-one insertion of the text seems like it would be real slow, especially as the worksheet started to fill up. Also, because the OP wants to "watch" the cells fill in, I figured we would not be turning off screen updating either. As for memory... my system has 4 Gigs of which I think Vista sees 3.2 Gigs... couple that with Windows/Vista's Paging File and I'm thinking it could take awhile on my computer. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... I don't know, Rick, I'm thinking it will start with the bells and whistles within the first one or two minutes unless the OP has some giga giga bytes of memory. I have 512 mb and I don't think that would last too long in xl2003. "Rick Rothstein" wrote in message ... That is not a practical request. Consider this... there are 16,777,216 cells on a single worksheet. I don't know how fast they could fill in; but, for the sake of argument, assume 100 every second (that's a rate you would have trouble being able to follow cell-by-cell). Since there are 86,400 seconds in a day, it would take a little more than 1.94 DAYS to watch that one, SINGLE worksheet fill in... and that calculation is without breaks for bathroom, eating or sleeping! You couldn't possibly watch an entire workbook of these worksheets fill up until Excel finally "blew up". -- Rick (MVP - Excel) "Louie" wrote in message ... Thank you guys for your willingness to help out. I know the macro I am asking for help with is a little different. For what its worth I think this is really great and it's very useful to me. However your comments are very funny and have been making me laugh very hard. Thank you for the many chuckles. I am wondering is there a way to set up this macro so that I can see the contents being entered in every cell one by one and the columns will autofit to the contents from the beginning? "Jim Cone" wrote: Ok, but I would like to know the file size. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message I definitely don't want to know why the OP wants to do this.<g Given that, would it be more efficient (can you imagine I'm asking about efficiency for an application like this<vbg) if instead of inserting a sheet and then populating it like this... ' create new sheets and fill them ad infinitum ' until VBA has a meltdown. Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets Set WS = .Add(After:=.Item(.Count)) End With WS.Cells.Value = "I am a winner" Loop couldn't we just copy any one of the previously populated worksheets and put after any existing sheet? I'm thinking of something like this... Do Until False ' loop until you run out of memory With ThisWorkbook.Worksheets(Worksheets.Count) .Copy After:=ActiveSheet End With Loop By the way, I am *not* willing to test this code.<bg -- Rick (MVP - Excel) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
For what it is worth...
Just two days ago, I attempted to create 10,000,000 unique (10 character) alphanumeric serial numbers on a xl2003 worksheet and then on a xl2002 worksheet. (somebody had offered to pay me) Excel crashed (several times) under both versions at somewhere over 9 1/2 million numbers. At just under 9 1/2 million numbers the file size was 232,000 KB. Programming Excel is never dull. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message You may be right but, again, I'm unwilling to test it. I just figured the interface between VB and the worksheet tends to be slow and doing a one-by-one insertion of the text seems like it would be real slow, especially as the worksheet started to fill up. Also, because the OP wants to "watch" the cells fill in, I figured we would not be turning off screen updating either. As for memory... my system has 4 Gigs of which I think Vista sees 3.2 Gigs... couple that with Windows/Vista's Paging File and I'm thinking it could take awhile on my computer. -- Rick (MVP - Excel) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Macro
You guys are so funny. LOL! What about 100 worksheets with 50,000 cells each?
"Jim Cone" wrote: For what it is worth... Just two days ago, I attempted to create 10,000,000 unique (10 character) alphanumeric serial numbers on a xl2003 worksheet and then on a xl2002 worksheet. (somebody had offered to pay me) Excel crashed (several times) under both versions at somewhere over 9 1/2 million numbers. At just under 9 1/2 million numbers the file size was 232,000 KB. Programming Excel is never dull. <g -- Jim Cone Portland, Oregon USA "Rick Rothstein" wrote in message You may be right but, again, I'm unwilling to test it. I just figured the interface between VB and the worksheet tends to be slow and doing a one-by-one insertion of the text seems like it would be real slow, especially as the worksheet started to fill up. Also, because the OP wants to "watch" the cells fill in, I figured we would not be turning off screen updating either. As for memory... my system has 4 Gigs of which I think Vista sees 3.2 Gigs... couple that with Windows/Vista's Paging File and I'm thinking it could take awhile on my computer. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
Macro line too long? | Excel Programming | |||
HELP - Too long coding for Macro | Excel Worksheet Functions | |||
Macro too long | Excel Programming |