Long Macro

[Louie]

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

 

[Chip Pearson]

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)

 

[Rick Rothstein]

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>

 

[Chip Pearson]

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)

 

[JLGWhiz]

Not only don't want to know why, don't want to know the outcome!

 

[Jim Cone]

Ok, but I would like to know the file size. <g>
--
Jim Cone
Portland, Oregon USA



"Rick Rothstein" <[email protected]>
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>

 

[Louie]

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?

 

[Rick Rothstein]

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

 

[JLGWhiz]

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]

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)

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.

 

[Jim Cone]

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"
<[email protected]>
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.

 

[Louie]

You guys are so funny. LOL! What about 100 worksheets with 50,000 cells each?