Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
I have managed to produce my first macro
The only problem is the layout, I wonder if any one has a script for the following BATMAN CAPER WINGS BATMAN LEATHER CAROL SONGERS BATMAN SPONGE BATS SCARE ME ROBIN PUFF THE ROBIN MAGIX DRAGON ROBIN LIVES IN THE BRONX Above is the original layout (not the content, I made that up :-)) I want a macro to output it like: Batman CAPER LEATHER SPONGE WINGS CAROL SINGERS BATS SCARE ME Robin PUFF THE MAGIX DRAGON LIVES IN THE BRONX I know that this can be done in a pivot table, but thats not the way I want to do it!!! Anyone help me pleeeassse -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
Batman CAPER LEATHER SPONGE WINGS
CAROL SINGERS BATS SCARE ME Robin PUFF THE MAGIX DRAGON LIVES IN THE BRONX I was miss led with these input boxes, Carol Singers and Bats Scare Me should be on the same line DAMM YOU INPUT BOX -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
You changed patterns--once you went down column B, then to column C (for
batman). But for robin, you went across per row. This goes across row by row: Option Explicit Sub testme01() Dim curWks As Worksheet Dim newWks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim RngToCopy As Range Dim DestCell As Range Dim iRow As Long Dim oRow As Long Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 0 For iRow = FirstRow To LastRow If .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value Then 'same key, apend to the far right Set RngToCopy = .Range(.Cells(iRow, 2), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) With newWks Set DestCell = .Cells(oRow, .Columns.Count) _ .End(xlToLeft).Offset(0, 1) End With Else oRow = oRow + 1 Set RngToCopy = .Rows(iRow) Set DestCell = newWks.Cells(oRow, 1) End If RngToCopy.Copy _ Destination:=DestCell Next iRow End With newWks.UsedRange.Columns.AutoFit End Sub Add headers to the first sheet if you don't have them already. "Danny Boy via OfficeKB.com" wrote: I have managed to produce my first macro The only problem is the layout, I wonder if any one has a script for the following BATMAN CAPER WINGS BATMAN LEATHER CAROL SONGERS BATMAN SPONGE BATS SCARE ME ROBIN PUFF THE ROBIN MAGIX DRAGON ROBIN LIVES IN THE BRONX Above is the original layout (not the content, I made that up :-)) I want a macro to output it like: Batman CAPER LEATHER SPONGE WINGS CAROL SINGERS BATS SCARE ME Robin PUFF THE MAGIX DRAGON LIVES IN THE BRONX I know that this can be done in a pivot table, but thats not the way I want to do it!!! Anyone help me pleeeassse -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
Hi Dave
Thanks for your response, I'm sure that there is nothing wrong with your script but it won't work on my workbook. Please ignore my first 2 posts. I have now changed my mined and want to do it from the format below I have 2 Columns will this style info: Batman CAPER Batman CLOWN Batman FAIRY I would like a macro to output: Batman CAPER CLOWN FAIRY Please note that all the data will be different but the columns will remain the same with all the reports that need this alteration. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
The existing macro worked fine for me with your both samples of your data.
What happens when you try it? Make sure you point at the correct sheet (I used Sheet1): Set curWks = Worksheets("sheet1") and remember to: Add headers to the first sheet if you don't have them already. "Danny Boy via OfficeKB.com" wrote: Hi Dave Thanks for your response, I'm sure that there is nothing wrong with your script but it won't work on my workbook. Please ignore my first 2 posts. I have now changed my mined and want to do it from the format below I have 2 Columns will this style info: Batman CAPER Batman CLOWN Batman FAIRY I would like a macro to output: Batman CAPER CLOWN FAIRY Please note that all the data will be different but the columns will remain the same with all the reports that need this alteration. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
See response to your other thread
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Danny Boy via OfficeKB.com" <u15117@uwe wrote in message news:5aff349762952@uwe... I have managed to produce my first macro The only problem is the layout, I wonder if any one has a script for the following BATMAN CAPER WINGS BATMAN LEATHER CAROL SONGERS BATMAN SPONGE BATS SCARE ME ROBIN PUFF THE ROBIN MAGIX DRAGON ROBIN LIVES IN THE BRONX Above is the original layout (not the content, I made that up :-)) I want a macro to output it like: Batman CAPER LEATHER SPONGE WINGS CAROL SINGERS BATS SCARE ME Robin PUFF THE MAGIX DRAGON LIVES IN THE BRONX I know that this can be done in a pivot table, but thats not the way I want to do it!!! Anyone help me pleeeassse -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any help with a little script
Thanks Dave and Bob
Dave, I was getting an error at destcell, I put a on error resume next command in, inserted a blank row on my data sheet as the program started at row 2 (I did try to change that to row 1 but it outputted the data wrong) Once again thanks for all your help -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need some VB script please | Excel Discussion (Misc queries) | |||
Need Help with Script | Excel Programming | |||
VB Script | Excel Programming | |||
Help with VB script please? | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming |