|
|
|
|
|
|
BMW Garage | BMW Meets | Register | Today's Posts | Search |
|
BMW 3-Series (E90 E92) Forum
>
OT - need excel help
|
|
09-11-2007, 09:09 AM | #1 |
Major General
740
Rep 7,308
Posts
Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK
|
OT - need excel help
I'm pretty handy with excel stuff normally but I'm stumped on my latest request from the boss. Google hasn't helped either.
I have to count the number of cells by colour? I've posted it up here http://spreadsheets.google.com/ccc?k...j94XA&hl=en_GB So for example on the first sheet (data) I need a total somewhere that counts how many yellow rows there are, how many grey etc etc. HELP
__________________
|
09-11-2007, 09:12 AM | #2 |
Colonel
96
Rep 2,572
Posts
Drives: AW DCT M3
Join Date: Feb 2007
Location: So.Cal
|
Cant you do an "If" statement formula?
__________________
|
Appreciate
0
|
09-11-2007, 09:13 AM | #3 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
Is someone manually colouring the rows? Would be much easier to use conditional formatting and a code to indicate the failure level?
e.g. a scale of 1 to 7 and then use conditional formatting to format the whole row based on that value. In the absence of that I think you'll be down to VBA to count the colours. I can have a go if you want? I'm not aware of any formula to detect colour.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-11-2007, 09:16 AM | #4 |
Major General
740
Rep 7,308
Posts
Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK
|
Yes - the colour is done manually - I've had a quick play in VB with this UDF
Function SumByColour(CellColour As Range, SumRange As Range) Dim cell As Range Dim SumColour As Double Dim MySum Application.Volatile 'If CellColour.Cells > 1 Then Exit Function SumColour = CellColour.Interior.ColorIndex For Each cell In SumRange If cell.Interior.ColorIndex = SumColour Then MySum = MySum + cell.Offset(0, -2) End If Next cell SumByColour = MySum End Function found here:- http://www.mrexcel.com/archive2/7400/8232.htm but couldn't get it to work
__________________
|
Appreciate
0
|
09-11-2007, 09:20 AM | #5 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
OK, here's one, glad you know VBA!
Public Function mycount(oColourToLookFor As Range, oRangeToSearch As Range) Dim oCell As Range Dim iFound As Long iFound = 0 For Each oCell In oRangeToSearch If oCell.Interior.ColorIndex = oColourToLookFor.Interior.ColorIndex Then iFound = iFound + 1 End If Next mycount = iFound End Function Basically you point it at the colour you want to look for (i'd put this say next to your key and point it at each one -ie repeat the call for each one, and point it at the range you want to search (i.e. a single column of those resuls)
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-11-2007, 09:23 AM | #6 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
Here's my example with formula showing:
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-11-2007, 09:29 AM | #7 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
Of course, it means you have to keep the range size updated unless you somehow can call it with a named range...otherwise when you add a row the range won't include that row. Might be other ways if that proves a problem.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-11-2007, 09:36 AM | #9 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
NP. Glad to help, anytime.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-12-2007, 01:01 PM | #10 |
Major General
740
Rep 7,308
Posts
Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK
|
More help needed - I'm rustier than I thought.
Ok - I've done this vb to select rows dependant on a string search then copy teh row to another sheet but I want to modify it to search for multiple strings and then copy those rows to the other sheet. Sub yellowtoescalatedfaultsbyequipmenttype() ' messagebox MsgBox "The magic will now start. The screen is going to flash repeatedly now!", vbInformation, "Health & Safety warning" Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 19 LSearchRow = 19 'Start copying data to row 54 in Sheet2 (row counter variable) LCopyToRow = 54 While Len(Range("AW" & CStr(LSearchRow)).Value) > 0 'If value in column AW = "Critical Fault reported to ADST", If Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST" Then 'Select row in Data to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Escalated faults by Equip Type in next row Sheets("Escalated faults by Equip Type").Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste 'Move counter to next row LCopyToRow = LCopyToRow + 1 'Go back to Data to continue searching Sheets("Data").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A1 Application.CutCopyMode = False Range("A1").Select Exit Sub Err_Execute: MsgBox "An error occurred - Please call 5404 8080 and ask for ***." End Sub
__________________
|
Appreciate
0
|
09-13-2007, 01:49 AM | #11 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
So when you look or the relevant string don't you just want to say Or.....then look for the other string(s) ?
I.E. Adding an Or statement where the string gets compared then another string comparison and repeat as many times as you need?
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-13-2007, 04:27 AM | #12 |
Major General
740
Rep 7,308
Posts
Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK
|
i.e
If Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST"or"etcetc" Then Pretty sure I tried that and it failed - not at work at the minute so can't check it again. Is that what you mean?
__________________
|
Appreciate
0
|
09-13-2007, 04:29 AM | #13 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
If (Range("AW&Cstr(LSearchRow)).Value = "Critical Fault reported to ADST") Or (Range("AW&Cstr(LSearchRow)).Value = "1stnewcarlovesipodsandheknowsit")
you can't just repeat the string bit as each bit after a logic operator (OR, AND etc.) will be evaluated on its own merit. So you were asking the question: "etcetc" ? which doesn't stand on its own. The computer is thinking "yeah...what about it".
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-13-2007, 08:22 AM | #15 |
Major General
740
Rep 7,308
Posts
Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK
|
Get a compile error:-
Expected:list separator or ) ? I tried moving the brackets around but no different.
__________________
|
Appreciate
0
|
09-13-2007, 08:26 AM | #16 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
If your code above worked then this should (may be positionging number/brackets in my example above):
If (Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST") Or (Range("AW" & CStr(LSearchRow)).Value = "some other text") Then looks fine to me?
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
09-13-2007, 08:41 AM | #18 |
Colonel
51
Rep 2,022
Posts
Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK
|
Thanks very much! NP, anytime.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS Previous: z3 3.0 Silver with Black&Red Leather |
Appreciate
0
|
Bookmarks |
|
|