MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I print my cells formatted in a different order?


Posted by Al on October 13, 2001 8:18 PM

I have an excel (97) database of cells filled with text. The width of each cell varies.
The cells are as follows:
A1, B1, C1.......R1
A2, B2, C2.......R2
A3, B3, C3.......R3
etc

What I am trying to do is print my cells like this
Line 1: A1 B1 G1 H1 I1 L1
Line 2: C1 M1 N1 J1 Q1
Line 3: D1 E1 F1 P1 K1 R1
Line 4: blank
Line 5: A2 B2 G2 H2 I2 L2
Line 6: C2 M2 N2 J2 Q2
Line 7: D2 E2 F2 P2 K2 R2
etc

How do I do this? Do I create a form? I want this format for printing only. I cannot find any example similar to this so I wonder if it can be done in Excel 97.

Al


Posted by Al on October 13, 2001 8:24 PM

More specific:
B1 is the same size as B2, B3....

Where ** is a placeholder (in other words, I want
B1, C1 and D1 to be in the same column, I want G1, M1 and F1 to be in the same column.

Posted by Jonathan on October 13, 2001 8:50 PM

Can you set up a second sheet with these references in the second sheet and just print the second sheet?

Like in sheet2 you would have, for example, in B1 it would say =Sheet1!B1, in B2 it would have =Sheet1!C1, in B3 it would be =Sheet1!D1; and so on, just setting up the second sheet the way you want the stuff to print.

HTH

Posted by Al on October 14, 2001 11:31 AM

Good idea. Thanks, but is there a way I could write that into a macro or something. My database has about 400 entries (goes from A1-A400, B2-B400, etc)

Al
====================================


Posted by Jonathan on October 14, 2001 2:53 PM

I didn't see any pattern I recognized in your original posting. I have good luck with VBA if there's a pattern I can refer to in code. How would VBA know which cells are referenced to which cells?

On the other hand, if you have to do the same thing at, say, monthly intervals, then you might consider the macro recorder: record what you do and then either play it back, or look at the code and tweak it further.

HTH,


Posted by rob on October 14, 2001 5:01 PM

I think he means this as the pattern:
Line 1: A1 B1 ** G1 H1 I1 L1
Line 2: ** C1 ** M1 N1 J1 Q1
Line 3: ** D1 E1 F1 P1 K1 R1
Line 4: blank
Line 5: A2 B2 ** G2 H2 I2 L2
Line 6: ** C2 ** M2 N2 J2 Q2
Line 7: ** D2 E2 F2 P2 K2 R2
etc to.....
Line ?: A400 B400 ** G400 H400 I400 L400
Line ?: **** C400 ** M400 N400 J400 Q400
Line ?: **** D400 E400 F400 P400 K400 R400

What is VBA?

Rob
========


Posted by Jonathan on October 14, 2001 7:39 PM

VBA is Visual Basic for Applications. It's the coding aspect of any MSOffice application (it's the lanuage macros are recorded in nowadays).
What I mean by pattern, is something a machine would understand. For instance, if I want to add the TAX to a list of PRICES, and the TAX is a certain percent, say 7.5%, then my code will have some lines like

For Each Item in PRICES, Price = Price * 1.075.

So the machine will STEP through each Item in my PriceList and add the tax to the price.

I don't see any pattern I can 'talk' to a machine about in Al's particular way of setting up the second page.

Is that any clearer (sorry if it isn't)?

Jonathan