how to repeat (loop) a macro statement 2

[bac2u]

HI,

Using relative cell references I've created a macro using the macro recorder, where beginning at cell A1 I copy the contents of A1 to the nine blank cells below it (so I end up with A1:A10 containing the same data. The data changes in every 10th cell of column A, and at each of those changes I want it to copy that cell to the 9 blank rows below it. So I want to end up with the "X" in A1:A10, "Y" in A11:A21, "Z" in A22:A31, etc.

The macro works just fine for the A1 to A10, but I've not been successfully in my attempts to make it loop, or repeat. any suggestions would be appreciated.

 

[KenWright]

Sub FillBlanks()

Dim i As Long

i = 0

For i = 0 To 10
Cells((i * 10) + 1, 1).Resize(10, 1).Value = Cells((i * 10) + 1, 1)
Next i

End Sub


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

Hi,

All kinds of ways to skin a cat...

    For i = 1 To 3
      x = Cells((i - 1) * 10 + 1, 1).Value
      For j = 1 To 9
        Cells((i - 1) * 10 + 1 + j, 1).Value = x
      Next
    Next

hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Saves you having to change the formula to reflect how many values you have in Col A:-

Sub FillBlanks()

Dim i As Long
Dim c As Long
Dim iLastRow As Long
Dim Rng As range

i = 0

iLastRow = Cells(Rows.Count, "A&quot.End(xlUp).Row
Set rng = Range(Cells(ActiveCell.Row, "A&quot, Cells(iLastRow, "A&quot)
c = Application.WorksheetFunction.CountA(rng)

For i = 0 To c
Cells((i * 10) + 1, 1).Resize(10, 1).Value = Cells((i * 10) + 1, 1)
Next i

End Sub

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[bac2u]

Thanks for the responses. Do I use any of your suggested options along with my current macro, and if so, where do I add the suggested code? Sorry, I'm a novice (like you can't tell!)


Sub Macro9()
'
' Macro9 Macro
' Macro recorded 9/12/2003 by CouilliardB
'

'
Range("A1&quot.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A9&quot.Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select
End Sub

 

[SkipVought]

Use Ken's code instead of your code. His is a better approch than mine.

BTW, notice that Select Method is not used and the Copy Method is not used.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bac2u]

Ken and Skip -- thank you for your suggestions. I tried Ken's and it works great as long as there are 10 rows between cells. I discovered after using your macro that I do have some instances where there are 11! Anyway, that's my problem -- thanks again for your help.

Barb