Tuesday, April 05, 2005

How to complete Bart Simpsons lines in Excel

Using a very simple piece of code this will take the text in the activecell and repeat it 99 times (giving a total of 100) in the cells below.
Make sure you have your cursor placed on the cell with the text you want to repeat.
Copy this code into a Module in a VBA Project (Use ALT F11 to open the VBA Project Editor).
Run the code (F5).

Dim linetext, i
Sub write_lines()
'Macro written by www.spyjournal.biz April 2005
linetext = ActiveCell.Value
For i = 1 To 99
ActiveCell.Offset(i, 0).Value = linetext
End Sub

Try it yourself. See what other uses this could be put to. For Next loops are very useful when writing code that has to perform the same function over and over again..