Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
How copy format, font, color and border without copy/paste? | Excel Programming | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |