Copy until new value
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Copy until new value
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Copy until new value
 

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