Carmen has attended:
Excel VBA Intro Intermediate course
Copy cells by using do while
I am trying to copy the value in cell C9 into the next 4 rows vertically. After that copy again the value in cell C14 and paste it into the next 4 rows, after the value in C19 and paste it into th next 4, by repeating always the same sequence. I would like to repeat that process all along the column and being able to stop it when there is no more values after counting 4 rows.
I have done this so far, but the macro never stops. Any suggestions to make it work?
Range("C9").select
a = activecell.value
do until a = ""
do until activecell.value <> a
If activecell.offset (1,0) = "" Then
selection.copy
activecell.offset(1,0).select
activesheet.paste
else
activecell.offset (1,0).select
End if
Loop
a =activecell.value
Loop
End sub
RE: copy cells by using do while
Hi Carmen
Thanks for getting in touch. There's a bit of a clash in the code as the loop stops when there is an empty cell immediately below the ActiveCell, however it's only going to copy when there is an empty cell below the ActiveCell.
Does the following help?
Sub Paste4Cells()
Range("C9").Select
Do Until ActiveCell.Offset(4, 0) = ""
a = ActiveCell.Value
For i = 1 To 3
ActiveCell.Offset(i, 0) = a
Next i
ActiveCell.Offset(4, 0).Select
Loop
End Sub
* The loop runs until the 4th cell down is empty
* When it picks up a value, it copies it to the three cells underneath
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
