Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Loop a macro in Excel
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Loop a macro in Excel
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. "flecky" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Loop a macro in Excel
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 "flecky" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Loop a macro in Excel
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 "flecky" wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Loop a macro in Excel
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 -- Don Guillett SalesAid Software "flecky" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a vlookup command in a for next loop in a macro in excel | Excel Discussion (Misc queries) | |||
how to put a loop in a macro? | New Users to Excel | |||
How can I setup an Excel Macro to loop? | Excel Discussion (Misc queries) | |||
Excel Macro loop problems | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) |