Posts by p45cal

    Re: Omit Null Values Calculating Average

    The cell formula that I used was long (I'm sure it could have been shorter, but I stopped developing it as soon as I got the right answer) and I couldn't incorporate the
    if(iserr(longformula,"",longformula again)
    construct since it became too long. The results produced a lot of div by 0 errors where there were no values to average. The macro cleans that up.
    Being a long formula it would be easy to introduce mistakes so I set it in stone, so to speak, in the macro.
    Disadvantages include having to change the macro if the number of records changes, although it could easily be accommodated with an extra line (maybe 2), but it was way past my bedtime.
    If I were to spend more time on it I should be able to shorten the formula (perhaps with sumproduct) to a point where it's more manageable without a macro.


    Re: Omitting Null Values When Calculating Averages

    Coming in a bit late on this one (9 hours) and going directly against "If possible, I'd like to keep the structure of the code as below with only the minimal modifications to address this issue", I've checked the results of the following in several places and it seems right (but check!), but it does do all the columns and it is quite short (all the calculations are finished by the first 4 lines):


    Re: Running Total Based On Specific Criteria

    Quote from Luna

    ..For instance, if John B. is entered, to immediately tell you his total? As opposed to having ..

    In a copy of the spreadsheet (to prevent altering the original during testing), follow the suggestions above, and try it (since it doesn't appear that you have done this).

    Re: Running Total Based On Specific Criteria

    Quote from Luna

    But doesn't that code have to specify a specific student? (eg, if A2= John B., then it would sum all of his suspensions, but not anyone else's).


    Quote from Luna

    And what if someone new was entered into the system? Would a new formula have to be created?

    Not if you switch this facility on:
    [COLOR="DarkSlateBlue"]Extend formats and formulas to additional rows
    By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows.

    Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.

    You can turn this option off (or back on again) at any time:

    On the Tools menu, click Options.
    On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off.
    To turn automatic formatting back on again, select the Extend data range formats and formulas check box.[/COLOR]

    Re: Restrict Users Cell Selection

    re:"I'm trying to force the user to only select one column"


    should return 1 then? so check for that too?
    (I'm coming in at this point not having read the whole thread, so sorry if this response is not appropriate.)

    Re: Macro To Delete The First Two Characters Of A Cell

    Quote from StephenR

    edelauna: all very confusing. Which columns are you using? You're looking for the last row in column B (LR), and then you're looking at column F (aiStr = Split(Cells(lRow, 6), " ")). If that's the relevant column you need to amend p45cal's code.

    Confusing indeed! The words 'goalposts' and 'moving' spring to mind. I spend time answering one line queries which edelauna should be able to answer for himself and spend more time trying to weedle out the specifics from him. The result? Not a reply, no answers to the questions asked of him, [COLOR="Wheat"][SIZE="1"](no 'thanks' either)[/SIZE][/COLOR] but an amendment of his previous post where he deletes half of it and then just tacks a block of code on the end and asks 'any advice?'.
    The important line of which is now

    aiStr = Split(Cells(lRow, 6), " ")

    Where's the relevance to the title of this thread?
    Why are we now delimiting with a space instead of a '-'?
    What on earth is in Cells(lrow,6)? Unfortunately I don't have telepathic abilities.
    Why the transfer of the data from one array to another.. and then some?
    What went wrong with previous suggested solutions?

    Edelauna, if you can't be bothered to spend time describing your problem lucidly, just tell me why others should bother spending time deciphering/guessing/mindreading/answering it?

    All the above questions are rhetorical, since I'm bowing out of this thread - no I'm not, I'm running away as fast as I can!

    Re: Macro To Delete The First Two Characters Of A Cell

    Quote from edelauna

    what does mid and split do?

    Copying the code, putting the cursor on the appropriate word and pressing F1 gets you an answer much faster than posting this sort of question here, however, from Help:
    [COLOR="Gray"]Mid Function

    Returns a Variant (String) containing a specified number of characters from a string.


    Mid(string, start[, length])

    The Mid function syntax has these named arguments:

    string Required. String expression from which characters are returned. If string contains Null, Null is returned.
    start Required; Long. Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").
    length Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.[/COLOR]

    In this case it effectively removes the first 2 characters from a string, start being 3.

    Again from Help:
    [COLOR="Gray"]Split Function

    Returns a zero-based, one-dimensional array containing a specified number of substrings.


    Split(expression[, delimiter[, limit[, compare]]])

    The Split function syntax has these named arguments:

    expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
    delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.

    The expression above is, in this case, the result of the Mid function while the delimiter is "-", the minus sign.

    Quote from edelauna

    My other problem is there is a double digit I would only like to delete one character?

    The strings you supplied both had double digits. Could you be specific about what you mean here please. Which double digits do you mean? Which single character do you want deleted. I am neither a mind reader, nor can I see over your shoulder.

    Quote from edelauna

    .. is there some way to make the characters I want a variable?

    Well, the result of the Split function is to result in an array with 2 members, x(0) and x(1), being (in the case of the string you supplied "| 12- 4") "12" and " 4" respectively. So you have your variables.

    Quote from edelauna
    x = Split(Mid(Cells(lrow, "E").Value, 3), "-")
    Cells(lrow, "F") = x(0) + x(1) / 12

    This code does not work?

    I don't understand the question mark at the end. Have you tried the code? It certainly worked for the two strings you supplied, yielding 12.3333333 and 60.91666667


    Re: Macro To Delete The First Two Characters Of A Cell

    Instead of

    feet = Left(Cells(lrow, "E").Value, 4) 
            inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do.
            Cells(lrow, "F") = feet + inches


    x = Split(Mid(Cells(lrow, "E").Value, 3), "-")
    Cells(lrow, "F") = x(0) + x(1) / 12

    but I'm not sure what you mean by "multiplied by 1%"
    p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from StephenR

    To remove the first two characters:


    which nicely removes the last two characters! Substitute 'LEFT' with 'RIGHT' and you're getting somewhere, but


    does it too.:smile:

    Re: Rank & Sort Table

    Right, now I'm beginning to see what you're trying to do. A low average is a high priority, and RANK as you've used it ranks higher numbers with lower numbers, so in the formula you add a COUNT statement of the whole range then subtract the rank from it. I hope so. [I didn't examine your formula closely the first time (out of laziness). I just adjusted it to give what I thought you wanted (and still think you want).]
    Did you know there is a third argument of the RANK function; if it isn't 0 (the default if omitted) it does all this for you.

    [SIZE="1"]In the following formulae I use G6:G14 instead of your G6:G80 because I wasn't going to create that much data for testing.[/SIZE]

    So in H6:
    copied down to H14, gives the lowest average a rank of 1, but where avarages are equal it gives the same ranking (of course).

    That might be all you want, but the COUNTIF part of your formula says 'no'. I'm guessing that after sorting on column G, instead of seeing a ranking like:
    you want to see:
    in which case in H6:
    copied down does this and incidentally, gives the same result as my adaptation suggested in message #2 above. Realise that by doing this, you are artificially ranking some projects lower than others when they originally had equal ranking, and that the ones that get the higher rankings is not under any specific control.

    Finally, sort ascending on column G.

    I really hope this answers your question,

    Re: Sort With Formulas

    I noticed that too. The OP's formula appears to turn equal rankings into a sequential ranking. Sorting the raw range still results in not being able to see the non-unique items' rankings in ascending order (using the OP's original formula). My guess is that the OP did sort on the raw range first, but noticed the out-of-order rankings, so went on to sort on the rankings themselves and was disappointed to see that they were still in the wrong order!

    Re: Ranking And Sorting

    It's your formula that forces the apparent sort order of non-unique items.

    Try changing the formula to:

    BTW, I used the formula below in my testing and it worked and have tried to derive the formula above from it to match your ranges.. there may be a mistake, so I include the working formula below.
    =COUNT($A$1:$A$6)-(RANK(A1,$A$1:$A$6)+ COUNTIF(A1:$A$6,A1)-1)+1


    Re: Array And Range

    At first glance I would do as Parsnip suggests which I think is change:

    Function function1( ai As Double, a As  Range) As Double


    Function function1( ai As Double, a As  Variant) As Double


    Re: Fill MultiColumn Listbox With Part Of Array


    myStart = 3: myFinish = 6
    For i = myStart To myFinish
    ListBox1.List(ListBox1.ListCount - 1, 0) = variable(i, 1)
    ListBox1.List(ListBox1.ListCount - 1, 1) = variable(i, 2)
    Next i


    Re: Set Focus To Application After Hiding UserForm

    I tried to replicate the problem but came a cropper when trying to use

    DoCmd.OpenForm "UserForm1"

    A bit of investigation seems to indicate this is an Access vba command. When I replaced

    DoCmd.OpenForm "UserForm1"



    there was no problem with getting focus on the spreadsheet.

    Presumably then, you have an Access object which you're activating from Excel so you may need to reactivate the Excel application from your code..
    p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]re:"how to kill this window ?"


    Unload UserForm1


    Re: Copy Only Used Cells In Column

    Quote from Dave Hawley

    If we assume the active sheet is new and ONLY has data starting from B1 onwards;

    Msgbox ActiveSheet.UsedRange.Columns(2).Address

    Will return "A:A".

    I hadn't gone as far down the line in my thinking on this, and was ready to accept this but a bit of exploration gave a different result, viz.:
    (I hadn't filled the entire column B with data, only B1:G14.) XP Home, Excel 2003.

    By the same token, should

    Msgbox ActiveSheet.UsedRange.Range("A1").Address

    return $A$1? (It returned $B$1 here)
    p45cal [SIZE="1"]..ready to accept I've lost the plot![/SIZE]