Air Traffic Control Worksheet

This project was kinda thrown at me after the success of my last one which I required a LOT of help with. Due to the complexity of the If Then End statements required for this I decided I'll just ask for help overall.

Basically, it is an air traffic control worksheet designed to allow the operators to maintain an easy to use format for ensuring two aircraft are not assigned the same airspace.

The concept in itself is easy and the color is as well, it's when I have to factor in that an aircraft can be assigned its own space AND the surrounding space that makes it tricky.

Attached are two documents. The Excel sheet with the grid as I need it to be with a second sheet with a breakdown of the order of operations I need the macros to follow. The text document is basically the same thing as the second sheet in the Excel document except with examples of how the conflicts would arise. I'll provide a PowerPoint with screenshots of how it should look with certain data entered when I get the chance.

Not included in the order of operations is the need to revert the colors of the data table to clear and the data sheet to yellow when an entry is corrected from an error.

Now, normally I'd go through the trouble of pouring myself into books and trying to find help to problems online, but I don't have the time nor the Internet reliability for that. So, I need it done within 5 days, if possible, and due to the rush in which I need it I'm willing to pay upto 125$ via Paypal. If Paypal isn't good I'd be happy to find an alternative (I really need it done and I havn't a clue as to where to start).

Any help is greatly appreciated!

Re: Air Traffic Control Worksheet

Yes, my apologies for the PowerPoint. Appearantly .ppt is not a valid format to upload here. So here is the file: http://www.zrift.com/asc.ppt

Alright, I've updated the Excel document on for classification and added in the order of operations for changing colors after fixing a mistake. It's attached.

To answer the question about the xyz, on the ASC sheet of the Excel document is a grid, x represents the y-axis of that grid, y represents the x-axis of that grid. It's stupid, I know, I didn't make the grid or the format for writing the airspace designation. Yes, z is a bit confusing but not when you notice that x and y result in a 9x9 block grid of its own. z represents which block within that 9x9 square the aircraft is centered. So, 89CC4 is x = 89 on the y-axis, y = CC on the x-axis, and z = block 4 in the resulting 9x9 grid.

The tricky part is that some aircraft will have more than that square, but also the surrounding area. xyz+ would look like 89CC4+, the + meaning that the eight block surrounding that airspace belongs to that aircraft for those altitudes provided.

Now, for the altitudes, they will be a minimum of 1000 with a maximum of 30000. Another factor that makes it tricky is that no aircraft may occupy the same airspace within a thousand feet of another aircraft. This must factor in the allotted airspace for an aircraft, not limited to the actual altitude. So if a tanker is allotted the altitudes 15000 to 16500 feet, the nearest aircraft maximum must be at most 14000 and the nearest aircraft maximum must be at least 17500 within that given airspace.

Here's the main reason I don't think I can do it myself, I don't have the faintest clue on how to merge the regular blocks with the + block and STILL factor for the altitude differences within a given airspace.

As for the concern of screwing up the macro and causing airplanes to crash, you needn't worry about that, our operators want this for a data reference, not for actual controlling.

Honestly, any help is greatly appreciated, and I've convinced my SNCOs to let me take the time to get it right, so I have a little room for it to be working than a week, but I'd still like to get it done so I can get whatever tweaks they want done too. ^^'

Again, I thank you all for your interest, and not only is the payment source open to change but so is the price. This would be a major help to the operators and all I really care about is doing my job (I'm getting paid extra anyways ^^').

Just let me know what I can do to make it easier to understand. I've already made the operators make concessions in what they wanted for function, I'll be happy to do some more.

Re: Air Traffic Control Worksheet

Thank you for the quick replies, but I'm afraid I'm having issues with your scripting. When I click the Intialize Grid button is errors: Run-time error '91': Object variable or With block variable not set. So, I tell it to debug and it takes me to

Matrix.Interior.Color = RGB(255, 255, 255)

in the script.

I don't know if it's a version or add-on conflict, your codes not working for me, but it's a bit disheartening, though the code looks amazing. I never would've been able to do even that much myself.

One note though, I'm sure you already figured this out from the PowerPoint but the scripts need to be automated so that it updates as they enter the data. The button thing is more than good for creation and beta but ops doesn't want to have to execute an entry to find out if it's errored or not.

Thank you again, it's an amazing code, I just wish I didn't have these conflicts! ^^'

Re: Air Traffic Control Worksheet

Thank you so much. I'll have to look at this new version later, this computer doesn't have Excel, but I didn't find out that you had automated most of it. Turns out I had left the script in debug mode and didn't realize it. ^^'

Thank you for all the work, though, I really appreciate it.

Re: Air Traffic Control Worksheet

Ah, thank you so much. You've been a tremendous help.