I was pretty happy with this macro, as it allowed me to process an address every second using the hot key. I soon realized that I had 5,000 addresses and I didn't want to keep running the same macro over and over.

Using a Do...Loop, I could set up the macro to run continuously. If I simply enclosed the recorded code with Do at the top and Loop at the end, VBA would continuously run my code over and over. This would allow me to sit back and watch the code do the work. This insanely boring task was now done in minutes instead of hours.

Note that this particular Do...Loop will run forever. There is no mechanism to stop it. That worked for the task at hand—I could watch the progress on the screen and after the program had advanced past the end of this database, I simply hit Ctrl+Break to stop execution:

Sub Macro3() Macro3 Macro

Macro recorded 10/29/2003 by Bill Jelen Move one address into database format.

Then move the cell pointer to the start of the next address. Keyboard Shortcut: Ctrl+Shift+A Do


ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste

ActiveCell.Offset(1, -l).Range("A1").Select Application.CutCopyMode = False Selection.Copy

ActiveCell.Offset(-1, 2).Range("A1").Select ActiveSheet.Paste

ActiveCell.Offset(2, -2).Range("A1").Select Application.CutCopyMode = False Selection.Copy

ActiveCell.Offset(-2, 3).Range("A1").Select ActiveSheet.Paste

ActiveCell.Offset(4, -3).Range("A1").Select

Loop End Sub

0 0

Post a comment