How to Loop a macro in Excel

[Guest]

I've looked at some of the other reponses, but I'm an absolute beginner,
because I haven't done macros for many years now.
All I'm trying to do is copy a cell down a column until it hits the next
value, then loop. There are irregular rows between each value.
The data might look like this:
50000
(I want the macro to insert 50000 here)
600023.4
(I want the macro to insert 600023.4
here)
(I want the macro to insert 600023.4
here)
945665

 

[Guest]

Just one question - what do we do when we hit 945665? If we try to copy it
until the next value is encountered, then it will be copied all the way to
the bottom of the worksheet. I doubt if that's what you're looking for.

 

[Guest]

In the meantime...
This routine will fill empties between numbers, stopping at the last number
in the column.
To run it, choose the cell with the first number in it that is to be used to
fill next empty cell. This doesn't have to be at the top of the column,
could be in the middle. Then choose the macro to fill in the blanks.

To get the macro into the workbook, press [Alt]+[F11] to open the VB Editor,
use the VB Editor's menu to Insert | Module and then cut and paste the code
below into the module, close the VB Editor and have fun...

Sub FillEmpties()
'choose cell with initial value
'in it before beginning this
'macro
'
Dim lastRow As Long
Dim currentValue As Variant
Dim rOffset As Long
'if in empty cell, get out
If IsEmpty(ActiveCell) Then
Exit Sub
End If
'find last row with something in it
'works for pre-Excel 2007 versions
'if using Excel 2007, change
' Rows.Count to Rows.CountLarge
'
lastRow = ActiveCell.Offset(Rows.Count - _
ActiveCell.Row, 0).End(xlUp).Row

Do Until ActiveCell.Offset(rOffset, 0).Row = lastRow
If IsEmpty(ActiveCell.Offset(rOffset, 0)) Then
ActiveCell.Offset(rOffset, 0) = currentValue
Else
currentValue = ActiveCell.Offset(rOffset, 0).Value
End If
rOffset = rOffset + 1
Loop

End Sub

 

[Guest]

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will do that nicely.

Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

No macro necessary. From a post by Bob

Select all the cells, use Edit/Goto Special, select Blanks, click OK
Now, type an equal sign, press the up-arrow, and Ctrl/Enter. You're done.

Bob Umlas