Posts by Brandtrock

    Re: Formula To Calculate Sales Commission

    Welcome to OzGrid!!

    A couple of quick questions:

    • The 5% commission is determined by?
    • The 20% commission is determined by?
    • What do you want the layout to be?
    • Is this homework?

    Obviously to calculate the commission you do a multiplication. This can be done in one or two columns. The single column method, you multiply by 1 + the commission %. The two column method you multiply by the commission % in one column and in the other, add the basis of the commission to the commission amount.

    Labels, formatting, etc are other considerations for you to consider.


    Re: Checking Formula's With F2

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    End Sub

    This will show the auditing arrows when a cell with a formula is selected.


    Re: Vba Coursework

    Forget the programming aspect for a second.

    As simplistic as this sounds, write down the steps that you would take to solve the problem by hand. What I'm suggesting is that you go back to junior or senior high school so to speak. Back then, when you learned a math concept, your instructor likely made you show each step of your work. This became tedious for me as I picked up on the concepts and saw shortcuts prior to being "instructed" in them.

    As an example, find the answer:

    (32 x 5) =

    Most adults would simply multiply 32 x 5. I'll spare everyone the formal names of the mathematical properties that allow this to occur. However, when first learning these properties, writing out the steps was required by my instructors.

    (30 x 5) + (2 x 5) =
    150 + 10 =

    This killed me as I wanted to "do it in my head" or write it as:


    where the 1 was the result of me carrying my answer from 5 x 2.

    My point is that by doing it the tedious, step by step way, I understood the mechanics of the multiplication better.

    Back to your task. If you would solve this on paper a certain way, then write down each of these steps, no matter how intuitive they seem. Coding all of these little steps will then produce your desired result. The smaller the task, the easier the coding.

    I hope this helps you out.


    Re: Vba Coursework

    Quote from Istupidheclever


    I'm not going to pretend, hold my head up high or bow out gracefully. Instead I am going to beg, admit defeat and pray for help. Yes I am blatantly asking someone to do my coursework for me and will take the insults by others for my actions.

    Well, at least you are honest.

    Quote from Istupidheclever

    ...Is this easy for you guys?

    Sitting at your PC and typing in some code is really the final step in the process. Writing down what your solution should be (in this case, the instructor has done that for you), perhaps making a diagram of your logic flow to help you understand how your solution should work, and then writing your code is easier.

    As with most things worth learning, doing is an integral part of making it easy.

    Quote from Istupidheclever

    I was introduced to the world of VBA a couple weeks ago and have been given this to do in the next two days, making up 100% of a module.

    I'm prepared to take general jibes or mockery for this potentially being extremely easy and me being dense, but any help would be appreciated.

    Obviously with the due date fast approaching, getting the completed assignment in is your most desirable outcome. Taking the grade you have earned (an F, I would presume, given your apparent panic) and repeating the course for a better grade (not all schools allow this) or asking for an incomplete and an extension of time to complete the assignment are more realistic outcomes. Of course, the latter would require that your instructor is familiar with you (ie., you didn't cut class frequently) and has seen an effort to learn on your part.

    Having done my time at Uni, I understand that not all instructors are human. Even with the most compelling of reasons to grant an extension, sometimes the power of the professorship overcomes them and they force a D or an F upon you. Take your lumps if that is the case.

    Quote from Istupidheclever

    "An operating theatre manager regularly needs to schedule operations for a list of patients; all operations are equally urgent. Operating theatres are an expensive resource in the hospital so the manager wants to use them as efficiently as possible, by scheduling the operations to take place over as few days as possible.

    So far, so good.

    Quote from Istupidheclever

    There are two surgeons who carry out the operations in separate operating theatres. The surgeons expect to work six hour days on average and will be very unhappy if they regularly work over seven and a half hours a day.

    Some meat and potatoes here. We know that we can schedule the doctors for up to 7.5 hours a day if necessary, but six is better.

    Quote from Istupidheclever

    For each patient on the list the manager knows which of two surgeons will carry out the operation...

    We are told who will perform each patient's operation.

    Quote from Istupidheclever

    ... and based on hospital records has calculated the expected (mean) operation duration and the standard deviation for similar operations.

    We are given how long the operation SHOULD take (mean), and how long it MIGHT take (mean + standard deviation). A tabular layout may be useful. We can see how long the doctors SHOULD and MIGHT work when we schedule operations in a theater for them.

    Quote from Istupidheclever

    A simple way of finding a feasible (but quite possibly sub-optimal) schedule for this problem is to use a greedy heuristic. We are going to use the following greedy heuristic in the coursework:

    This is most helpful as in the real world, a client or boss will likely not be this specific when a task is assigned. Not just a humble opinion, but some practical advice from someone who does this as a living.

    Quote from Istupidheclever

    1. Select a surgeon, S, whose schedule has not been planned.

    Not too hard, we only have two surgeons, so pick one.

    Quote from Istupidheclever

    2. Create an ordered list, L, of the patients who require treatment by that surgeon, in descending order of expected operation duration.

    An array of the patients, with a specific sort order is being requested. The provided code in the example workbook provides you with functions that you can use to determine the expected operation duration.

    Quote from Istupidheclever

    3. Consider the patient, P, at the top of L, awaiting operation O. Add P to the earliest acceptable daily schedule for the surgeon S. A daily schedule is acceptable for patient P if adding operation O will not:

    Before getting to the conditions, let's put this in plainer English. With our ordered list of patients, find the day on the surgeon's schedule that fits the operation the best.

    Quote from Istupidheclever

    (a) take the total expected duration of the operations for that day over 6 hours, or

    (b) take the total expected duration of the operations on that day plus the standard deviation for the total duration of operations on that day over 7.5 hours.

    Condition (a) limits us to days where an additional procedure won't take the hours beyond 6

    Condition (b) limits us to days where an additional procedure plus the standard deviation will not take the hours beyond 7.5

    Quote from Istupidheclever

    If no such acceptable daily schedule exists then create a new daily schedule at the end of the current schedule for surgeon S to accommodate patient P (and implicitly operation O).

    We must start a new day if we can't schedule the operation on an existing day.

    Quote from Istupidheclever

    4. Remove patient P from L

    Move on to the next patient.

    Quote from Istupidheclever

    5. If L is not empty, then return to step 3.

    If the list is not empty, then schedule the next patient.

    Quote from Istupidheclever

    6. If there are surgeons whose schedules have not been planned then return to step 1.

    Schedule the other surgeon's procedures.

    Quote from Istupidheclever

    In order to make the heuristic deterministic (i.e to guarantee the same result each time it is run on the same problem instance), we must specify how ties are broken in step 2. In this coursework, ties should be broken by selecting the patient with the highest standard deviation of operation duration first. If two patients have the same expected operation duration and standard deviation of operation duration, then the patient with the smallest index (first on the original list of patients) should be chosen first.

    Additional conditions on the list of patients. This would have been nice to know back in step 2. This, of course, is a prime example of why a pencil and paper approach is better than starting to code with your instructions at your side.

    Quote from Istupidheclever

    You must implement the greedy heuristic for the theatre scheduling problem in a VBA project in Excel. Your program must have a single user interface so that the user can:

    Single user interface. A userform would by my choice, but buttons on a spreadsheet would also do.

    Quote from Istupidheclever
    • read data from a text file
    • recognize any problems with the data
    • run the greedy heuristic
    • output the results.

    • Search OzGrid for reading data from a text file. Many examples exist. Run this from a button on a userform or from a button on your worksheet.
    • Error checking the data, your professor gave you some functions that may be useful here. Assign these to a button on a userform or from a button on your worksheet.
    • Run the greedy heuristic. Once you have it written, run it from a button on a userform or from a button on your worksheet.
    • Output the results, either to the screen, a printer, or give the user a choice. In any case, run this from a button (or buttons) on a userform or from a button (or buttons) on your worksheet.

    Quote from Istupidheclever


    Now I realize that this is not a completed VBA project for you. What it is, hopefully, is a point in the right direction. Going step by step, you should be able to ask direct, specific questions that will allow you to absorb some of what is being done along the way.

    VBA frequently presents more than one way to achieve a result. For example, nested IF-THEN-ELSE constructs and the SELECT CASE structure can both be used to determine which action will be taken given a set of constraints that are tested. Which one is best is debatable depending on the skill of both the coder and the end user.

    Asking questions here for a specific portion of the project MAY result in getting multiple solutions. When you have code for a portion of the project and it isn't working, post the code (using the code tags) so we can help you debug it.

    If you open the VBE and look carefully, your instructor has given you the name of two functions that you must complete. They are:

    Public Function TestFile(ByVal strFile As String) As FileError
        ' TO DO
    End Function
    Public Function GetAlgorithmResults(ByVal strFile As String) As clsResults
        ' TO DO
    End Function

    Take a close look at this subroutine:

    Private Sub ResultsDemo()
    'Demo code is in here.
    End Sub

    See if this sparks any thoughts or questions.

    Kindest regards,

    Re: All Looking A Bit Different?

    Quote from Jack in the UK

    nar nope it ant the Oz GhOzt its Dave Hawley just updating stuff, what do You think?
    BTW Hey good to see You buddy, trust all is good with You and Yours?


    I like the changes for the most part. The linking words thing is cool, would be neat to see it work on phrases like shg suggested as well.

    Now if I can just get my car to work properly all will be good. :mad:

    If I could find a mechanic that knew half as much about cars as the mods and admins here know about Excel, I'd be twice as far ahead as I am.


    Re: If/vlookup Function Returning N/a Error

    Don't forget to use ABSOLUTE addresses in the vlookup formula if you intend to copy it down.


    should be:



    Re: Looup Function Error Mismatching Data &amp; N/a Error

    Quote from Help File

    Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

    The LOOKUP vector needs to be one column or row as Reafidy stated, but the LOOKUP_VECTOR must be sorted ascending, your example is not. It is sorted ascending on Column A. Selecting A13:C32 and sorting on Column B will allow your formula to return the correct result, assuming you correct it to:

    =LOOKUP(A2,'lookup table'!$B$13:$B$32,'lookup table'!$C$13:$C$32)

    Re: Opposite Of Concatenate

    Not a lost cause at all.

    • You want to find the - in your string
    • The piece to the left is your feet
    • The piece to the right is your inches
    • Searching the piece to the right for a space lets you know if your value needs to end in .5
    • The inches are then either

    • Only the piece to the right of the dash OR
    • The piece between the dash and the space

    Using the MID, LEFT, RIGHT, and FIND functions will get you where you need to be.

    Post if you need further help with the exact functions.


    Re: Most Efficient Coding Method

    It would seem to me that setting the variable is more efficient because you aren't asking the code to get the bounds again. The variable holds the value assigned to it until a new value is reassigned or the variable goes out of scope. Putting the bounds in again asks your code to find them again.

    Probably not a huge performance issue in small routines, but coding efficiently in small routines seems to me to ensure that bigger projects would be coded as efficiently as possible because good habits are routine.

    Just my 2 cents worth,

    Re: Count Number Of Occurrences (not Incidents)



    will return 5 (given the data in the OP's example) which is the number of OCCURENCES of v, not the number of incidents (which is 3).


    will return 3 (given the data in the OP's example) which is the number of OCCURENCES of h, which was not asked about at all.


    Re: Visual Basic Editor - Creating Macro Prompts

    Welcome to OzGrid Frank.

    Is this homework?

    The instructions you referenced asked for an IF-THEN-ELSE structure to check for errant inputs, Reafidy gave you a SELECT CASE structure. This is the same way I would code this type of task.

    The IF-THEN-ELSE could be handled a few different ways, so until you let us know whether the supplied code will work for you, I will defer to that solution.


    Re: Clear Contents Vs Delete

    Nice explanation Far.

    Note that deleting moves B9 to B6.

    Note also that clear contents leaves the formatting behind. This may be desirable or undesirable.

    Perhaps not an issue in the current situation, but worth noting I think.