Posts by Cheeky Charlie

Re: Fill UserForm ComboBox With Values In Range

Yuck.
Why do you do this so often:

ComboBox(x).Value = ""
    Sheets("Sheet1").Range("y").Value = Me.ComboBox(x).Value

Instead of just deleting the contents of the cells?

Anyway, I think your range("date") is how you're populating the comboboxes. I think it might be better to cycle through the cells in "date" range with the

if<>0

code that Hyder's already shown you.

Re: List All Corresponding Cells To Lookup

G2 = VLOOKUP(Sheet1!A2,Sheet2!$A$2:$D$12,4,FALSE)

Copy down.

Seems too easy.

It would be possible to concatenate the cell values witihn the main formula, so you didn't need your "con" columns, but as you've done it, keeping them makes your formulae easier to read.

Re: Return Occurrence Number Of Lookup

Urk!

Aligning your cells has hidden the ghastly wealth of numbers entered as text in your worksheet! This makes it much harder to pivot because 75 as a number is pivoted as a separate entry to 75 as text (for example). Assuming you fix the way data is inputted (you can always error-correct "number stored as text") here is a pivot table which does what you are looking for

Your summary seemed to be in terms of size, not reference number, so I re-ordered the pivot to show the summary of all orders for each size (not just the first).

Better. Oh yes it is. Also, as noted by others earlier in the thread, I would definitely recommend you learn pivot tables, they are extremely powerful.

HTH

PS second time I've finished what Yard started... :wink:

Re: Prevent Worksheets From Auto Calculating

No it's not.

When you set it, you set it for the application - which applies it to all open workbooks. Any workbook saved with calculation manual will re-open with calculation manual even if you've turned calculation back on in the interim.

Test it properly.

Re: Prevent Worksheets From Auto Calculating

Comments about how unwise it is to use manual calculation have been made - I don't need to add to this.

Turning workbook calculation off (by code (

workbook.calculation = xlmanual

) or via tools->options->calculation) is a property saved with the workbook without needing to use an "on open" macro.

Obviously, this would mean none of your sheets would calculate unless you told them to.

HTH

Re: Deleting Querry Tables Only When They Are Active

Thanks for your charming response, it means a lot to those helping.

Re: Deleting Querry Tables Only When They Are Active

Use code tags! (I suspect you will be banned by the time you read this though)

It depends how good you want to be.
You could simply add:

on error resume next

At the beginning of your macro, this effectively ignores errors and doesn't process the lines which cause them.

More properly, clear the cells and delete the query tables:

Dim qTable as QueryTable
Cells.ClearContents
For Each qTable In Sheets("sheet1").QueryTables
    qTable.Delete
Next qTable

HTH

Re: Password Protect The Workbook With A Command Button

From help files (Protect Method):

expression.Protect(Password, Structure, Windows)
expression Required. An expression that returns a Workbook object.

Password Optional Variant. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

Structure Optional Variant. True to protect the structure of the workbook (the relative position of the sheets). The default value is False.

Windows Optional Variant. True to protect the workbook windows. If this argument is omitted, the windows aren’t protected.

ActiveWorkbook.Protect Password:="test", Structure:=True, Windows:=True

(Basically you need to define the password as part of the protection method)

HTH

Re: Workbook Creates Automatic Backup

File->
Save as->
Tools-> (top right of dialogue box)
General Options->
Uncheck Always create backup

(I presume you mean "functionally, why are backups created? (i.e. because someone has always create backups switched on) not "logically, why are backups created?" (i.e. because backups are a way of not losing work if your computer crashes, or there's a power failure, or you work with a circus-ring full of clowns who have a habit of ruining your work then over-saving it).

Re: Calculate Text As Value

I spent far too much of my life breaking down the formula.
Basically:
If you failed column L - it's all over, you're fired, answer = 0, otherwise:

Cols: B,C,D,E,F,G,I,M,N,O,P,Q,T,V (the columns in which we don't expect an NA) take the value and multiply it by the weighting for that column (in row 3)

***Slight divergence***
(you then divide all of these weightings by 2 - you should:
a: multiply by 0.5 instead, as per Yard
b: divide the whole thing by two once (i.e. a/2 + b/2 + c/2 = (a + b + c)/2)
c: or make your weightings half what they are now
d: or make another row, like row 3, to divide the weightings by two before you apply them
***Divergence over***

Cols: H,J,K,R,S,U test to see if the value is a number, if it is a number, do the same maths to it as the above, if it's not, double the halved weighting for that column(!!! who wrote this and have you looked for the on switch on their brain?) - excuse me, that was cheeky.

So, could I suggest that instead, we use one formula to check every cell for being a value (if it isn't, call it 5) and multiply all cells by their column weightings and add them?

=IF(L6=0,0,SUM(SUBSTITUTE($B6:$K6,"NA",5)*$B$3:$K$3,SUBSTITUTE($M6:$V6,"NA",5)*$M$3:$V$3)*0.5)
enter with Ctrl + shift + enter[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]This looks far less beautiful than it should because I had, effectively, to double the formula in order to accomodate for the "gap" at column L

Otherwise I feel smug

PS, it will only work with "NA", i.e. not "na" or "N/A"

PPS, actually mine's almost identical to Yard's solution, except I finished :wink: and mine uses an array formula whilst Yard's uses sumproduct (which is basically an array formula - correct me if too blase).