Kneel to count cells of a particular color about onlyoffice.github.io HOT 12 CLOSED

Thank you and everyone for your generous answers, will continue to support onlyoffice, you can close this now, good luck!

from onlyoffice.github.io.

@AlexeyMatveev686 thank you

from onlyoffice.github.io.

@askonev Please take a look

from onlyoffice.github.io.

et.getRange(color

Hello, let me help you.

You can use this macros for your task:

function CountColoredCells (Range1, Range2)
{
// Range1 is the range where the search will be performed
// Range2 is the range to be compared with when searching
// (if there is more than one cell, it will only be compared with the first one)
var count = 0;
var color = Range2.GetFillColor();
if (color !== "No Fill") {
Range1.ForEach(function(cell) {
var curColor = cell.GetFillColor();
if (curColor !== "No Fill" && curColor.color === color.color)
count++;
});
}
return count;
}

Also you can can check how it works in my example file.
Good luck!
new (1).xlsx

from onlyoffice.github.io.

Sorry, just replied. I still get an error after following your method of testing

.

from onlyoffice.github.io.

@Bobolovewill Hi!

Please specify the version of the product on which you run the script.

from onlyoffice.github.io.

@Bobolovewill嗨！

请指定运行脚本的产品版本。

Version: 5.6.5 (build:3)

from onlyoffice.github.io.

@Bobolovewill This is very very old version. Please update to current v6.4.2 and see if problems persists

from onlyoffice.github.io.

@Bobolovewill This is very very old version. Please update to current v6.4.2 and see if problems persists

Sorry for just replying, thanks everyone. The function worked after the upgrade, but I can't call it anywhere, I have to write it in advance. Last question, how to run macros automatically, or is there a shortcut?

from onlyoffice.github.io.

run macros automatically, or is there a shortcut

We got autorun button:

And marcos settings:

Not sure how it works, not my area

Also, you can convert your macros to plugin (it use same code, but require some additional metadata) - you can run plugin with autostart

from onlyoffice.github.io.

@ShockwaveNN Unfortunately, this macro does not work on version 7.5.1, could you please update it?

from onlyoffice.github.io.

@ShockwaveNN Unfortunately, this macro does not work on version 7.5.1, could you please update it?

Hello. If you use example whitch I posted you should add "var" before "oWorksheet" variable into the 3-th line. I've checked this macros, it works fine in 7.5.1 after this changes. Here is new macros:

(function()
{
    var oWorksheet = Api.GetActiveSheet();
    // the ranges where the search will be performed
    var Range1 = oWorksheet.GetRange("A1:B14");
    var Range2 = oWorksheet.GetRange("B1:B7");
    // the range to be compared with when searching
    // (if there is more than one cell, it will only be compared with the first one)
    var Range3 = oWorksheet.GetRange("D1");
    var Range4 = oWorksheet.GetRange("D1");
    var Range5 = oWorksheet.GetRange("D2");
    var count  = CountColoredCells(Range1, Range3);
    var count2 = CountColoredCells(Range2, Range4);
    var count3 = CountColoredCells(Range1, Range5);
    oWorksheet.GetRange("E1").SetValue("D1 in A1:B14 = " + count);
    oWorksheet.GetRange("E2").SetValue("D1 in B1:B7 = " + count2);
    oWorksheet.GetRange("E3").SetValue("D2 in A1:B14 = " +count3);
  
    function CountColoredCells (Range1, Range2)
    {
        // Range1 is the range where the search will be performed
        // Range2 is the range to be compared with when searching
        // (if there is more than one cell, it will only be compared with the first one)
        var count = 0;
        var color = Range2.GetFillColor();
        if (color !== "No Fill") {
            Range1.ForEach(function(cell) {
                var curColor = cell.GetFillColor();
                if (curColor !== "No Fill" && curColor.color === color.color)
                    count++;
            });
        }
        return count;
    }
    
})();

from onlyoffice.github.io.