Copy until new value

  • Thread starter Thread starter Shane
  • Start date Start date
S

Shane

Thank you in advance for any advice. I hope my problem is clear below:

I am trying to write some code to copy a cell (A1) with content in
column and paste to the cell below (A2) IF the cell is empty an
continue to paste (A1) to the empty cells below (A2....A10). If th
cell below is not emply (A11), copy (A11) and paste to the empty cell
below (A12...A14).

An addittional plus would be:
Stop when there are no values in a cell of the same row in the colum
to the right (B14).


The cells cannot be hard coded in the macro because every column wil
be different.

Thanks again for taking the time.

Shan
 
Sub Try()

'GO TO A1
Range("A1").Select

'GIVE A VARIABLE NAME TO THE VALUE YOU WANT TO REPEAT
MyValue = Range("G1")

'GO DOWN ONE TO GET STARTED
Activecell.offset(1,0).select


'START THE LOOP TO GO UNTIL THE CELL IS EMPTY
While Not Activecell = ""

'GIVE THE CELL SIX CELLS OVER THE COPY VALUE
Activecell.OffSet(0,6)= MyValue

'GO DOWN ONE
Activecell.Offset(1,0).Select
Wend

End Sub

-----Original Message-----

Thank you in advance for any advice. I hope my problem is clear below:

I am trying to write some code to copy a cell (A1) with content in a
column and paste to the cell below (A2) IF the cell is empty and
continue to paste (A1) to the empty cells below (A2....A10). If the
cell below is not emply (A11), copy (A11) and paste to the empty cells
below (A12...A14).

An addittional plus would be:
Stop when there are no values in a cell of the same row in the column
to the right (B14).


The cells cannot be hard coded in the macro because every column will
be different.

Thanks again for taking the time.

Shane


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
this may help

Sub rename()
For Row = 1 To 65000

If Sheets("sheet1").Cells(Row, 1) = "" Then
Sheets("sheet1").Cells(Row, 1) = Sheets("sheet1").Cells
(Row - 1, 1).Value
If Sheets("sheet1").Cells(Row, 2) = "" Then GoTo 12
End If
Next Row
12 Sheets("sheet1").Cells(Row, 1) = ""
End Sub

Igor
 
Back
Top