Copy cells by using do while

Resolved · Urgent Priority · Version 2007

Fri 10 Jan 2014, 13:44 Delegate Carmen said...

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

Mon 13 Jan 2014, 09:43 Trainer Gary said...

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

Mon 13 Jan 2014, 11:09 Delegate Carmen said...

RE: copy cells by using do while

Amazing. It works perfectly.

Thank you very much

Regards

Carmen

Excel tip:

Calculate age or service

The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip:

In a blank worksheet, type the birth date in cell A1, using slashes to separate day, month, and year.
In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.

View all Excel hints and tips

Copy cells by using do while

Copy cells by using do while

RE: copy cells by using do while

RE: copy cells by using do while

Excel articles

Excel tip:

Calculate age or service