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.

p45cal

Re: Center Across Selection Vertical

That's not what the OP was asking. The cells are not merged with Center-across-selection.

p45cal

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):

Sub blah()
With Sheets("Station_Averages")
    .Range("C2").FormulaArray = _
            "=AVERAGE(IF(ROW()-1=MONTH(Station_Comprehensive_Cleaned!R2C2:R52C2),IF(ISNUMBER(Station_Comprehensive_Cleaned!R2C:R52C),Station_Comprehensive_Cleaned!R2C:R52C)))"
    .Range("C2").AutoFill Destination:=.Range("C2:C13"), Type:=xlFillDefault
    .Range("C2:C13").AutoFill Destination:=.Range("C2:AS13"), Type:=xlFillDefault
    'Now clean up error cells and convert to values:
    For Each cll In .Range("C2:AS13")
    If IsError(cll.Value) Then
    cll.Value = ""
    Else
    cll.Value = cll.Value
    End If
    Next cll
End With
End Sub

p45cal

Re: Center Across Selection Vertical

I can't find it in xl2003 (win xp) either. I don't think it exists.
p45cal

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).
p45cal

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

No.

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]
p45cal

Re: Restrict Users Cell Selection

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

rselect.columns.count

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.)
p45cal

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!
p45cal

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.

Syntax

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.

Syntax

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.
[/COLOR]
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

Code

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

p45cal

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

try

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:

Code

LEFT(RANGE("A1"),LEN(RANGE("A1"))-2)

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

Mid(Range("A1"),3)

does it too.:smile:

Re: Create Sub Directories From Cell Values

see whether
http://www.cpearson.com/excel/MakeDirMulti.htm
is of any help.
Use it maybe thus:

mypathname = "C:\" & [A1] & "\" & [B1] & "\" & [C1] & "\" & [D1] & "\" & [E1]
MakeDirMulti (mypathname)

the names of folders were in cells A1 to E1

p45cal

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:
=RANK(G6,$G$6:$G$14,1)
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:
1,2,3,4,5,5,5,8,9
you want to see:
1,2,3,4,5,6,7,8,9
in which case in H6:
=RANK(G6,$G$6:$G$14,1)+COUNTIF(G$6:$G6,G6)-1
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,
p45cal

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!
p45cal

Re: Ranking And Sorting

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

Try changing the formula to:
=COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+COUNTIF(G5:$G$81,G5)-1)+1

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

p45cal

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

to

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

p45cal

Re: Cell Restriction

try changing Or to And
p45cal

Re: Fill MultiColumn Listbox With Part Of Array

perhaps

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

p45cal

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"

with

UserForm1.Show

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 ?"

perhaps

Unload UserForm1

p45cal

Re: Vb Copy The Content Of Upper Cells

just select the cells followed by

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;

Code

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.:
$C$1:$C$14
(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]