Dividing Data in one column to three columns

I have a VBA routine that looks at a list of scheduled sporting events and currently divides them up into two relatively equal columns; "like" events (on the same channel) are required to stay in the same column.

Now, I need to take the same data and spread it into three columns instead of two, and am stuck on the logic. The new columns are called "TC1", "TC2", and "TC3".

The present logic is if TC1 >= TC2, then the next event is assigned to TC2. How do I get TC1 = TC2 = TC 3 (approximately)?

Judy

Hi Judy,

I'm new to this forum but I think your question needs more detail to elicit a useful response.

What is the criteria on which the events are split into the three columns? Is it as simple as the channel value? I.e. do you just want this list:

Event.......Channel
Event1.......TC1
Event2.......TC3
Event3.......TC2
Event4.......TC2
Event5.......TC2

split into three columns based on the value of _Channel_ , i.e.

TC1...........TC2.........TC3_
Event1.......Event3.....Event2
................Event4
................Event5

Perhaps make and attach a workbook showing the data and the resulting three columns.

The code will be simple, but I'll wait until you clarify the question.

Sorry to answer a question with a question!

Hope that helps.

cheers

Malcolm

Thanks for your reply.

The code is pretty long, so I have bolded the most important section (about page 13)...

Basically, at this point in the program, I have separated the sporting events into several different classifications. The one in question (called "DSSE") at this stage has all of its events in a single array. The challenge is to:

1) Create a print document that displays all three columns (as will be described).

2) Create multiple print documents, one each for TC1, TC2, and TC3

What needs to happen is that each of the DSSE events (which have previously been sorted by viewer channel (VC) and begin time) are evaluated in a loop to decide whether they should be assigned to TC1, TC2, or TC3. If the event is on the same viewer channel as the previous event, it stays on the same TC; otherwise, the events should be fairly evenly divided amongst the three and then output in time order.

As mentioned, the current code supports two columns (TC1 and TC2), but needs to be expanded to three.

The original code was written by someone no longer with my company, so I have been limping it along for a couple of years--this is the first major change.

Let me know if you need any additional info...


************************************************************

OMG. Judy, I feel sorry for you having to make modifications to that mess.

The problem as you described it sounds simple. Unfortunately the code you have at present is an egregious mess. Almost unreadable.

It seems that you're just starting in VBA and you'll learn nothing from that code other than what an unmaintainable and hideous mess a person can create when mixing Excel and VBA.

Unfortunately I simply don't have the time or the background understanding to work through that code and help you out. What I suggest you do is:

- Spend the half day it will take to patiently following through the code and really get your head around what it's doing. Then if possible modify it or re-write that part of the code from scratch.

- Once you understand what the code is doing I suspect you can restate the problem and write something clean and simple, from scratch, with help from this forum as required.

My first introduction to Excel VBA was modifying similar code. Created by a person who thought "excellent, with VBA and Excel I can do anything...". After a number of years I've come to the conclusion that the only way to write applications in VBA which are in any way maintainable and debug-able is to:

- Use Excel for input, output and storage only.
- Do all but the simplest calculations in VBA.
- Avoid using native Excel data manipulation tools (e.g pivots tables etc) in VBA.
- Use object-orientated design principles (i.e. encapsulate your data in appropriate classes which contain the calculation/functionality which arises from that data, etc)
- Avoid at all costs procedural code which reads and calculates from Excel in a big whirl-wind of reading, calculating and writing. Or stated another way, don't write applications like macros.

Excel + VBA almost encourages you to write bad applications. It lures you into breaks all the normal rules about separation of data, logic and presentation layers.

Sorry for the little (and probably unwanted) lecture. Unfortunately seeing that code just reminded me what a mess can be made.

OK, enough. Good luck.

cheers

Malcolm