Ask Your Question
0

Create loop in range cell for count

asked 2017-12-11 10:26:54 +0200

ypkdani gravatar image

updated 2017-12-11 17:22:34 +0200

Lupp gravatar image

Hello,

i need to count the cell with the same background color in a range of cell. The range is define as :

oRange = sheet.getCellRangeByPosition(7, nRiga+startIndex, 8, nRiga+stopIndex)

and in this range i use a condition

oRed4Condition(0).Name = "Operator"
oRed4Condition(0).Value = com.sun.star.sheet.ConditionOperator.NOT_BETWEEN
oRed4Condition(1).Name = "Formula1"
oRed4Condition(1).Value = climit
oRed4Condition(2).Name = "Formula2"
oRed4Condition(2).Value = "-" + climit
oRed4Condition(3).Name = "StyleName"
oRed4Condition(3).Value = "Senza nome1"

I want to count the cells that are true for this condition. i'm tring with

for each c In oRange.CellFormatRanges
  if (c.CellBackColor =     oRed4Condition(3).Value) then
    checkResult = checkResult + 1
  endif
next c

but oRange.CellFormatRanges is not correct... how i can loop all cell and check this condition?

Thanks Daniele
(Edited for better readability by @Lupp )

edit retag flag offensive close merge delete

Comments

Styles used in conditional formatting are only applied for the view and never assigned to the cells. Therefore you cannot find the respective cells testing for properties of such a style. You need to evaluate the condition as @JohnSUN demonstrated.

Lupp gravatar imageLupp ( 2017-12-11 17:31:40 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2017-12-11 10:45:54 +0200

JohnSUN gravatar image

updated 2017-12-11 10:47:12 +0200

Do you mean this?

for each c In oRange
    if (c.Value < -climit) or (c.Value > climit) then
        checkResult = checkResult + 1
    endif
next c
edit flag offensive delete link more
0

answered 2017-12-11 18:05:30 +0200

Lupp gravatar image

updated 2017-12-11 18:06:08 +0200

You (the OQ) should consider to get the result using COUNTIF().
like in
{=COUNTIF(ABS(H12:I32);">"& 2)} e.g. or, now parametrized:
{=COUNTIF(ABS(OFFSET(INDIRECT("A1");nRiga+startIndex;7;startIndex-stopIndex+1;8-7+1));">"& cLimit2)}

edit flag offensive delete link more
0

answered 2017-12-13 10:54:02 +0200

ypkdani gravatar image

Thanks to all!!

edit flag offensive delete link more

Comments

This is not an answer, so please delete it and add a comment under the question instead. Also, if one of the answers is helpful then please click ✔ to mark it as correct.

Jim K gravatar imageJim K ( 2017-12-13 18:24:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-11 10:26:54 +0200

Seen: 931 times

Last updated: Dec 13 '17