Macro question

[Keith Kaminetzky]

Just started trying my hand at these.

I have the following module which copies data from two cells side by side,
copies it to the two rows beneath and keeps going. I want this to repeat
till it runs out of entries. I know the technique is looping but no idea
where to start. here is the code
TIA KK

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/15/2003 by
'
' Keyboard Shortcut: Ctrl+n
'
ActiveCell.Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A2").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A2").Select
ActiveSheet.Paste


End Sub

 

[Sandy Mann]

Keith,

If your data is in the form of data in A1,B1 with two blank cells bellow
then data in A4,B4 with two blank cells etc. then, (as I found from these
NG's you don't actually need a Macro.

Select the range to be filled in - say A1: B12
Select Edit > Goto > Special > Blanks
then enter an equals sign, (in A2 which will be automatically selected), and
then click into cell A1 to produce the formula:
=A1 and enter the formula by pressing and holding the Ctrl while you press
Enter and the job will be done.

If you want, you can record a Macro to do it. I did and got:

Sub Macro1()
' Macro1 Macro
' Macro recorded 16/11/2003 by Sandy Mann
'
Range("A1:B12").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A13").Select
'
End Sub

The Macro Recorded records cell selections but as I (again) found out in
these NG's it is almost never necessary to select anything in Macros so if
we replace the Selections with *With* statements we get:

Sub trial()
With Range("A1:B12")
With .SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=R[-1]C"
End With
End With
End Sub

If there is no data below the data to be filled in then the first *With* can
be replaced with:
With Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row + 2)
to give the required range


HTH

Sandy