Macro question

  • Thread starter Thread starter Keith Kaminetzky
  • Start date Start date
K

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
 
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
 
Back
Top