Automate Report -- Macro Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

galacticwino

Thread Starter
Joined
Apr 5, 2004
Messages
3
I have data in excel like this:

1
aaa
bbb
ccc
ccc
ccc
2
aaa
aaa
ccc
3
aaa
bbb
bbb
....

that I need to repeatedly make into a specific landscape format like this:

1 aaa bbb ccc
..............ccc
..............ccc
2 aaa ccc
...aaa
3 aaa bbb
........bbb
---------(PERIODS ONLY FOR ALLIGNMENT PURPOSES)---------------------

Any ideas how to start this. I use VB frequently, but the multiple rows (like in series 1 of several "ccc") are confusing me. Any tips would be appreciated!! Thanks,

Wino :confused:
 
Joined
Aug 30, 2003
Messages
2,702
>> Any ideas how to start this

Are those the only options per number? Either "aaa" or "bbb" or "ccc" (regardless of how many)?

Rgds,
Andy
 

galacticwino

Thread Starter
Joined
Apr 5, 2004
Messages
3
Actually, there would be 4 options rather than 3. Each row contains several cells worth of data. And the key identifier is in that first cell, column A-- that's where one of the 4 options is.

And then I would copy the set number of cells every time with the identifier and the data, as opposed to just copying one cell-- such as columns A through G in that row. Does that make sense?

Thanks for your help!

Wino
 
Joined
Aug 30, 2003
Messages
2,702
>> Does that make sense?

I'm afraid not (to me, at any rate).

You say "each row contains several cells worth of data" yet the original example looks like a single column.

Maybe you could zip your file & upload it?

Rgds,
Andy
 

galacticwino

Thread Starter
Joined
Apr 5, 2004
Messages
3
Sorry about the confusion -- here is one tab with an example of the original data, and one tab with the required output. Thanks again for looking into this!

wino
 

Attachments

Joined
Aug 30, 2003
Messages
2,702
Even with seeing it I can't figure all the possible parameters, however I knocked up a sub to reconfigure 1st sheet data as per 2nd sheet (NB - I don't do proper code).

>> Any ideas how to start this

Well ya gotta start somewhere. ;)
I inserted a duplicate of col A to the left of col A then stripped out the text cells, as a marker for the sub. Also formulas in D1:F1 to calc # of rows per "aaa" or "bbb" or "ccc" per number.

Select A2 & run the sub, it'll convert the B3:G6 data and end on A7, from where you can run it again (CTRL+Y). I've not looped this to run for all entries in (new) col A as I may be miles down the wrong road already.

You'll see that to top'n'tail you'd need to delete B:G then run down col A deleting any wholly blank rows.

I only ask that you don't tell me if you've fixed this already, either by code, or manually having given up on code.

HTH,
Andy
 

Attachments

Joined
Aug 30, 2003
Messages
2,702
wino,

In case you check back sometime, one section (at least) of the code can be pruned a fair bit. The 6-line section

'x = ActiveCell.Row + 1
'Selection.End(xlDown).Select
'y = ActiveCell.Row
'Selection.End(xlUp).Offset(1, 1).Select
'NumRows = y - x
'Selection.Resize(NumRows, 1).Select

can be replaced with

Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeBlanks).Offset(0, 1).Select

NB - this is all one line ; watch for text wrap.

Rgds,
Andy
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top