[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
filo62
Newbie

Joined: 24 Mar 2006
Posts: 1

 Posted: Fri Mar 24, 2006 3:06 am    Post subject: [CALC] Function sum by color Hello, I wish to write a function which makes the sum of the cell whose backcolor is blue example : sumcolor(A1:A5,255). attention A1:A5 is not a selection by mouse and not string I wish to be able to stretch the cells to recopy the formula. And that it remakes calculations automatically like the function "sum" Thanks for you help
ChrisK
General User

Joined: 25 Jan 2006
Posts: 17

Posted: Tue Apr 11, 2006 11:58 am    Post subject:

 Code: Sub SumByColour() dim oDoc, oSheet, oCell  oDoc = ThisComponent  oSheet = oDoc.sheets.getbyindex(0) ' Set values and background colours for cells  oSheet.getcellbyposition(0,0).value=5  oSheet.getcellbyposition(0,3).value=7  For I = 1 to 2      oCell=oSheet.getcellbyposition(0,I)      oCell.cellBackColor = RGB(0,0,0)      oCell.value=I  Next I  For I = 4 to 5      oCell=oSheet.getcellbyposition(0,I)      oCell.cellBackColor = RGB(0,0,0)      oCell.value=I  Next I  ' Sum by the background colour  For I = 1 to 5      oCell=oSheet.getcellbyposition(0,I)      If oCell.cellBackColor = RGB(0,0,0) then SBC=SBC+oCell.value  Next I  msgbox SBC End Sub
kumkum
Newbie

Joined: 30 Jun 2006
Posts: 1

 Posted: Fri Jun 30, 2006 11:24 am    Post subject: modification needed, please Hi, I would like to start using calc instead of excel, but I despertly need to convert excel function. the function performs the following: ' returns the sum of each cell in the range InputRange that has the same ' background color as the cell in ColorRange ' example: =SumByColor(\$A\$1:\$A\$20,B1) ' range A1:A20 is the range you want to sum ' range B1 is a cell with the background color you want to sum Can you help please ? Tx
pitonyak

Joined: 09 Mar 2004
Posts: 3622
Location: Columbus, Ohio, USA

Posted: Fri Jun 30, 2006 12:01 pm    Post subject:

 Code: =SumByColor(\$A\$1:\$A\$20,B1)

The problem is that the function SumByColor will be passed raw data which is not associated cells. In other words, although I can extract the data, I can not tell you which cell contained the data. It is, therefore, not possible to sum by data using this method directly.

_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Sun Jul 02, 2006 10:02 am    Post subject:

I would use conditional formatting or function STYLE() in order to paint certain cells with a color and then get the sum from the same criteria.
If your colors are hard formatted by some user's fuzzy logic (they don't match calculable criteria), you can get this feature within a minute:
Install this module http://www.oooforum.org/forum/viewtopic.phtml?t=32909
 Code: Function CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%) Dim v    v = getSheetCell(vSheet,lRowIndex&,iColIndex%)    if vartype(v) = 9 then       CELL_BACKCOLOR = v.CellBackColor    else       CELL_BACKCOLOR = v    endif End Function

Having possibly colored cells in A1:A100 of this sheet:
formula B1: =CELL_BACKCOLOR(SHEET();ROW();1)
returns 0 if A1 is black
Drag down until B100
=SUMIF("\$B\$1:\$B\$100;0;\$A\$1:\$A\$100)
returnes the SUM of cells where RGB is zero (black cells).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
SacredCow
General User

Joined: 23 Dec 2009
Posts: 6

Posted: Thu Dec 24, 2009 8:47 am    Post subject: Sum Column By Text Color

Greetings All,

I have a spreadsheet that I'm migrating from Excel that had a macro to SumByColor whereby it would sum the contents of a column based on those fields with red numbers. I've attached the code below and while I imagine it may not work with OOo due to VBA I thought maybe there was a similar solution available in OOo?

Thank you for any assistance you can offer.

Sincerely,
-SC

 Code: Function SumByColor(InRange As Range, WhatColorIndex As Integer, Optional OfText As Boolean = False) As Double ' ' This function return the SUM of the values of cells in ' InRange with a background color, or if OfText is True a ' font color, equal to WhatColorIndex. ' Dim Rng As Range Dim OK As Boolean Application.Volatile True For Each Rng In InRange.Cells     If OfText = True Then         OK = (Rng.Font.ColorIndex = WhatColorIndex)     Else         OK = (Rng.Interior.ColorIndex = WhatColorIndex)     End If     If OK And IsNumeric(Rng.Value) Then         SumByColor = SumByColor + Rng.Value     End If Next Rng End Function
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Thu Dec 24, 2009 10:17 am    Post subject: Did you try the above CELL_BACKCOLOR function? Why not? Have you ever used a spreadsheet without the help of other people's VBA code?_________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
SacredCow
General User

Joined: 23 Dec 2009
Posts: 6

Posted: Sat Dec 26, 2009 1:27 pm    Post subject:

No I did not try the backcolor. Why not? Because a) I'm asking about FONT color and not cell back color, b) looking at the code this would not meet my needs. I'll be working with pairs of rows of data whereby one row of the pair will be marked with a color, however, only those cells with a value should be part of the sum. In your case every cell within the selected row would wind up being totaled in the sum.

 Quote: Have you ever used a spreadsheet without the help of other people's VBA code?

Wow, aside from the relevance I'm not sure I noticed any rules to posting a question. Perhaps don't offer an answer if your answer is of no value and you don't really feel like helping?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Sun Dec 27, 2009 11:09 am    Post subject:

Another programmer who does not program.
The variable inspector in the Basic IDE as well as any of the frequently mentioned helper tools show you all the properties of a cell within seconds.
A simple stop mark in the Basic IDE reveals instantly how the function is supposed to work.
Simply copy the function, rename it, replace "CellBackColor" with "CharColor" and you have your function. Same trivial replacement for any other cell property.
 Quote: =sumbyfontcolor(E2:E57)

 Code: X2 =CELL_CHARCOLOR(SHEET();ROW();5)=16711680

returns TRUE if the color is "light red". Copy down that formula.

=SUMIF(X2:X57;TRUE();E2:E57) or any other aggregation, pivot tables, filters, DSUM, subtotals, consolidation, SUMPRODUCT applies to the calculated field now.

There are thousands of possible shades of red which is why the whole approach is bullshit.
I would copy the X column, paste-special numbers and replace the formatting in E with a conditional one. No macros, no cry with a clear and compatible spreadsheet design.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
lrey1154
Newbie

Joined: 11 May 2010
Posts: 1

 Posted: Tue May 11, 2010 11:42 pm    Post subject: Test for font color I'm actually responding to an assignment where it looks like font color needs to be tested. I'm new to OO macros so bear with me. In one case the test for font color seems to work but in other it doesn't. This case does not seem to check color, also used numeric coded as in following code that does seem to verify font color test. Text value passes but there is a cell with white font color that isn't counted. For iRow = LBound(x, 1) To UBound(x, 1) For iCol = LBound(x, 2) To UBound(x, 2) oCell = Sheet.getCellByPosition(iRow, iCol) If x(iRow, iCol) = "XXXXX" Then If oCell.CharColor = rgb (255,255,255) Then XXXXXSumval = XXXXXSumval + 1 End If End If Next Next This code does seem to perform color test For iRow = LBound(x, 1) To UBound(x, 1) For iCol = LBound(x, 2) To UBound(x, 2) oCell = Sheet.getCellByPosition(iRow, iCol) If x(iRow, iCol) = "YYYYY" Then If oCell.CharColor = 16777215 Then YYYYYSumval = YYYYYSumval + 1 End If End If Next Next Initially had nested ifs in single function then broke up into individual functions with same result.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum