Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell Macro help
I have text in a spreadsheet, not wrapped text but single line per row.
I wish to create a macro that will copy and paste th address/state/zip/phone info into seperate cols to the right of th name. I have created a macro that will only paste to Row #1. HOW.... do I designate the selected box(ie name #2 or cell #A2) to b the starting point for the macro as I work down the page. (Current) Bob Smith 1234 turkey Ave Putsville, UT 12345 (999)999-9999 Bill Smith 1235 Hen peck road butte, Mt 65432 (999) 555-5555 (Wanted in 5 cols) Bob Smith/1234 turkey ave/putsville, UT/12345/(999)999-9999 Bill Smith/1235 Henpeck Rd/etc...........get the idea? The macro will cut and paste in different cells on the same row bu repeats itself to the original row when I procede down the page. I built a macro but it is tied to the original cell number. How do I lose the cell number distinction so I can work my way down th document. I have 300 of these.... PLEASE HELP, Do -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell Macro help
Don,
Suffice to say, ensure that you have a backup of your data before executing this macro. The macro keys off of the active cell and works its way down the active cell column. Select a cell in the first block of data that you want to change. I assume this is at the top of the list. The macro programmatically transfers the data and then deletes the unnecessary rows. It does not select nor copy and paste. There is no need to. Sub TransposeToCols() Dim Rng As Range, C As Range Dim i As Integer Set Rng = ActiveCell.CurrentRegion i = 0 Application.ScreenUpdating = False Do Until Rng(1) = "" For Each C In Rng i = i + 1 Rng(1, i) = C Next Range(Rng(2), Rng(i + 1)).EntireRow.Delete Set Rng = Range(Rng(2), Rng(2).End(xlDown)) i = 0 Loop Application.ScreenUpdating = True End Sub Regards, Greg -----Original Message----- I have text in a spreadsheet, not wrapped text but single line per row. I wish to create a macro that will copy and paste the address/state/zip/phone info into seperate cols to the right of the name. I have created a macro that will only paste to Row #1. HOW.... do I designate the selected box(ie name #2 or cell #A2) to be the starting point for the macro as I work down the page. (Current) Bob Smith 1234 turkey Ave Putsville, UT 12345 (999)999-9999 Bill Smith 1235 Hen peck road butte, Mt 65432 (999) 555-5555 (Wanted in 5 cols) Bob Smith/1234 turkey ave/putsville, UT/12345/(999)999- 9999 Bill Smith/1235 Henpeck Rd/etc...........get the idea? The macro will cut and paste in different cells on the same row but repeats itself to the original row when I procede down the page. I built a macro but it is tied to the original cell number. How do I lose the cell number distinction so I can work my way down the document. I have 300 of these.... PLEASE HELP, Don --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell Macro help
To Greg Wilson,
:) 1000 camels laden w/ treasure awaits you in heaven. That was exactly what I had hoped for. Your Macro script worked like a charm, Thank you thank you thank you..... Do -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Excell 03/07 | Excel Discussion (Misc queries) | |||
Excell Macro Help Please | New Users to Excel | |||
converting Lotus 123 macro to Excell macro | Excel Discussion (Misc queries) | |||
Macro Text into Excell | Excel Discussion (Misc queries) | |||
macro crashes excell xp | Excel Programming |