Posts by AoN

    Re: VBA Cell Value Update

    Wow, that does exactly what I was looking for.

    I know the code is a bit wasteful in that it forces an update to all of the cells in the range at the same time, but that also ensures that all entries are validated regardless of what is modified.

    As for an example of what I would ideally want to see happen, if you enter 3 into E62, it updates to 3*D62, or 17,490. Having it update all the fields at once is cool, but I do understand it takes more effort for the system to do so. So, is it possible to limit it to updating only the modified field if the field was in the specified range, or would that be getting too complex of code that it wouldn't be worth doing?

    Short story: I need to update a budget template to automatically multiply a cell value by that of another. There are two ranges involved, E62:P75 and E84:P86. Right now I'm just trying to get it working with the first range. In the corresponding D for a cell is the per month rate. For each month E:P, you should be enter in an integer and the script then replaces that value with the value times per month rate. If the field is blank or not an integer, then it defaults to 0.

    I keep getting "Run-time error: '-2147417848 (80010108)': Method 'Value' of object 'Range' failed".

    Here's my code:

    I've also attached the spreadsheet. Any suggestions?

    Re: VBA Conditional Formatting

    I tried swapping out the "G" for "G:G" with no change to my script. I even tried replacing all the ranges with it following the same scheme, start column:end column. :S

    I'm working on a spreadsheet that incorporates military date formats and needs specific formatting based upon earlier columns and the date of the cell. I have all the colors in RGB format, and I was hoping it would work, but it didn't. I don't know if it was my attempt to code it or something, probably both. Attached is an example of what I would need something to look like, and here is the VBA I had originally tried (I used several examples from across the web to put it together ^^'):

    The VALUE(TEXT(YEAR(TODAY()),"0000")&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")) is my own creation to format the value to how I have to have the date entered into the cells. I've already confirmed this works (basic functions is something I'm actually good with ^^).

    Here is a basic break-down of how I need it to work:

    I know, my attempt at the actual code looks nothing like that. My attempt was also trying to simplify it, which is where I think I screwed up.

    Anyways, anyone think they can help?

    Oh, about the "ü" thingies (in Wingdings font they're check marks), it's in order of precidence, so if I have a check in column D, I don't care about A-C. Basically, the highest check will determine the color.

    Thank you in advanced for simply looking at this. ^^

    Re: Change fill color of all dependent cells when value changes

    Quote from AAE;512539

    Run your code in the worksheet_calculate event.

    My apologies but I don't know how I would have to modify the code for that. I know the opening of the code, but simply changing that didn't do anything cept make it error. :S

    The title says it all. I have a cell range with 5 possible entries and need the backgrounds color coded to these entries. Some entries are dependant on other entries, so the change of one cell would effect others. It is very simple in design:

    P = Yellow
    T = Green
    X = Gray
    F = Dark Green
    R = Red

    For individual modification I have it set-up with:

    When I enter a P in one cell it will change another cell to a T, requiring both cells to be recolored, but this script only seems to do one cell. To make it simple, I'd like it to update the entire cell range based upon the individual cell contents whenever any single cell is modified.

    Any ideas?

    Re: Air Traffic Control Worksheet

    Thank you, dmeek. Though you are thinking along the lines of operations, not a bad thing, it's a bit too along those lines. Operations has a system for controlling aircraft factoring in everything from altitude to speed to anything else they need. This spreedsheet is meant to be a tool for the assignment of airspace, not aircraft control. Basically, the only factors of any concern are altitude and assignment, the actual location of the aircraft is irrelavent due to the fact the aircraft is given free reign over the assigned airspace within the assigned altitudes.

    Don't get me wrong, it's a brilliant program, but in truth it will hinder the overall system. I love the code though! Gave me a trig refresher to figure it out. ^^'

    Thank you for your help, I just wish I could use it.

    Re: Air Traffic Control Worksheet

    Thank you, RoflJ, but as I stated in my last post, Ger Plante will be finishing the project. I appreciate your code, it's rather unique, but further assistance is not required.

    Re: Air Traffic Control Worksheet

    I thank everyone for their help in this, but Ger Plante has done a wonderful job in getting the project to its omega version in a fraction of the time I had expected it to go. He's agreed to stay on for the completion of the project and it's turned out very well so far.

    I have read the concerns of some of the other programmers, to include that the code is difficult to edit. This is true, and it's an issue we are facing now with adding in our final features to the project, but that is also a good thing. The operators, whom tell me what they need/want from this, are not exactly the most computer savvy, let alone cooperative people (they try to be maintainers when we tell them not to be [that's my job and I dislike having to fix something they broke trying to do my job]). I've little doubt they would attempt to mess with the coding of the program to further adjust it to their desires to "test" new ways of doing things. Ultimately, that would probably result is making the entire project a complete waste, whether the code is simple or not. At least with a complex code they are far more discouraged from even attempting such a thing.

    I do appreciate everyone's support and willingness to help, and I should've mentioned sooner that Ger Plante had already taken on the project in full, but your assistance is always welcome. I'm afraid I won't be able to pay anyone else, but if anyone wishes to make contributions you are more than welcome to do so.

    This has been a great learning experience for me, the code everyone has submitted is amazing compared to how I would have gone about even the simpliest of features. I thank all of you for all your help.

    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

    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. :S

    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

    Yes, my apologies for the PowerPoint. Appearantly .ppt is not a valid format to upload here. So here is the file:

    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. :S

    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. ^^

    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: Pull Characters From Alpanumerics and Right Align

    I can't believe I didn't get this before! You guys are all amazing, I just didn't put things together.

    Although none of the code snippets did EXACTLY what I wanted, every part I needed was in a version of the code!

    After have brooded over it for a few days I figured it out, thanks heavily to everyone for helping.

    You guys are absolutely amazing and I'm sorry for all the trouble I caused. ^^'

    I must ask one more thing though, could someone please remove that attachment from my last post, I'm afraid I don't know how. :S

    Re: Pull Characters From Alpanumerics and Right Align

    For this I'm uploading a fairly complete copy of the form. I removed certain fields due to...issues.

    Anyways, with the exception of the fields I had to remove that is the actual document I've been working on with ALL the code and the different versions of them that I've used. I did edit the code provided to use a cell for dynamic serial numbers instead of the static text of the original codes provided.

    Other than that, I don't recall editing it, and due to my current situation I can't compare the code from here to that of the document. ^^'

    With the current code set as SerialNo I get a syntax error. :S

    Re: Pull Numbers From Alpanumerics

    Thank you, the code worked perfectly to remove the hyphen, but it didn't do anything for placing the serial number into the ERO sheet. :S

    BU4 = T
    CA4 = H
    CG4 = M
    CM4 = 5
    CS4 = 9
    CY4 = 2
    DE4 = 8
    DK4 = 5
    DQ4 = 7
    DW4 = 3

    The problem isn't that I can't split it or right align it, but that the code is much to extensive, it is too difficult to edit should I have to edit it.

    For instance, if the serial number were GS5F-332 it would have to appear as:

    BU4 =
    CA4 =
    CG4 =
    CM4 = G
    CS4 = S
    CY4 = 5
    DE4 = F
    DK4 = 3
    DQ4 = 3
    DW4 = 2

    The issue is with splitting up the serial number into the individual blocks.

    When I defined SerialNo1 as the first letter to goto BU4 in the ERO sheet it will grab the first digit and left align it:

    BU4 = G
    CA4 = S
    CG4 = 5
    CM4 = F
    CS4 = 3
    CY4 = 3
    DE4 = 2
    DK4 =
    DQ4 =
    DW4 =

    If I work backwards, which is what I had originally done, it defines 10 strings for 10 cells and makes the last digit in the serial number the first serial number digit to be entered into the right block, moving left. This resulted in the last digit being copied, the first digit of the serial number, to repeat in every remaining block:

    BU4 = G
    CA4 = G
    CG4 = G
    CM4 = G
    CS4 = S
    CY4 = 5
    DE4 = F
    DK4 = 3
    DQ4 = 3
    DW4 = 2

    Is there a way to cut down the original code I put in my first post to make it appear like:

    Data Page

    AA4 = GS5F-332

    ERO Page

    BU4 =
    CA4 =
    CG4 =
    CM4 = G
    CS4 = S
    CY4 = 5
    DE4 = F
    DK4 = 3
    DQ4 = 3
    DW4 = 2

    That was the main thing I wished to solve, was the extensive amount of editing required to change a variable, whether to add a digit, remove a digit, or simply change how it shows up. I'm sorry for making it seem so...weird. Please, feel free to charge an extra question or two for the trouble. I got extra anticipating problems from my end. ^^'

    Well, my boss is technically the president of the United States. The US Military operates like that. ^^'

    Anyways, to answer your question, the actual form is classified, which is why I was required to remove everything from it. I'm sorry for the trouble it's caused, but I have no option in the matter.

    Also, for your question about extracting the serial number, I'm attempting to create a data sheet that will completely fill in the document as it is suppose to be filled in. On the actual document there are certain things that must be broken up by blocks, says someone 50 years ago when the form was first created. This is meant as a way for personnel that are not familar with filling out these forms to do so easily by simply providing the required information to one page and having everything else done for them.

    Lastly, the merged cells is also a problem I've had no choice but to deal with. The document was designed to print onto premade forms. On these forms the blocks overlap, requiring in an Excel document to use merged cells.

    Again, I must apologize for all the trouble this has caused, and any further assistance is greatly appreciated. ^^