Increase a number based on cell style

[Jwalex86]

I have Indesign cc 2017 on windows.

I have inherited a 300 page price book that was built in Indesign, and want to know if there is a way to apply a price increase to cells based on the cell style we have already applied. I know very little about scripts or VB but I do have access to an IT department that knows VB. However, they don't know anything about Indesign. I have included a sample image of one of our tables that we need to apply a price increase to. The cells containing prices do already have cell styles applied, so we are looking for a way to use a price increase script (or any other way) to increase the prices (by 1.017%). We have made it work in excel and we don't how to make it work in indesign.

Code that works in excel:

Sub tester()

Dim rng As Range

Dim curVal As Single

Dim newVal As Single

For Each cell In ActiveSheet.UsedRange.Cells

    If cell.Interior.Color = RGB(255, 109, 109) Then

        curVal = cell.Value

        newVal = curVal * 1.017

        cell.Value = newVal

    End If

Next

End Sub

Sample of a Table

[Liphou]

Hello

Should values be rounded?

Thank you

Bonjour

Faut-il arrondir les valeurs?

Merci

[Liphou]

Cela devrait vous aider.

var mesArticles = app.activeDocument.stories;
for (var i=0; i < mesArticles.length; i++) { // for 01
        for ( var j= 0; j < mesArticles.tables.length; j++) { // for 02
            for ( var k=0; k < mesArticles.tables.cells.length; k++) { // for 03
                 try {
                     if (mesArticles.tables.cells.fillColor.colorValue.join(',') == '255,109,109') {
                        mesArticles.tables.cells.contents =  tester(mesArticles.tables.cells.contents);
                  }  // fin du if
                } catch(e) {
                 // pas de couleur      
               }
              } // fin for 03 */ // pour trèté une celure
         }// fin for 02
    } // fin for 01
function tester(numbers) {
    // $.writeln('tester : ' + numbers.slice(0,1));
     var numberTmp;
         if (numbers[0] == '$') {
                   numberTmp = numbers.slice(1);
                   numbers = '$'+leCalcul(Number(numberTmp));
               } else {
                   numbers = leCalcul(Number(numbers));
               }
    return numbers;
}
//
function leCalcul (leNumber) {
    var lePourcentage = Number('1.017');
    return (String(Math.round(leNumber*lePourcentage)));
}//

Bonne journée à vous

[Jwalex86]

Thank you!

[Jwalex86]

Kasyan Servetsky pointed me in this direction of a program that works off of character style. My document doesn't use character style. So, I have tried to change it to cell styles with some minor success. The drop down shows my cell styles but it updates every number ignoring the cell styles. I am sure a noob with code like me missed something simple...

https://indesignsecrets.com/free-script-to-change-all-numbers-in-a-document-using-math.php

I'm not sure how Liphou posted the code or I would post what I have.

[Liphou]

Yes, it is always easier to work with a style of character, we do a research on the style and we treat the results, in your case it is necessary to test each cell.
In script database I put online, it does not handle much possible cell content errors but it is a very good foundation, we can make it put a style. (This is a lot of calculation and testing)

Oui, il est toujours plus facile de travailler avec un style de caractère, on fait un recherche sur le style et on traite les résulta, dans votre cas il faut tester chaque cellules.

Dans base de script que j'ai mis en ligne, il ne gère pas beaucoup les erreurs possible  de contenu de cellule mais c'est une très bonne base, on peux lui faire mettre un style .  (c'est long beaucoup de calcul et de test)

Dropbox - PourTest.zip

[Jwalex86]

In Indesign character styles aren't used unless you want to deviate from the paragraph style though. Instead of character style couldn't we use cell style or paragraph style?

[Jwalex86]

Here is the one I have been working on. If you want to change every cell in the table it works great. If you want to change based on cell style if crashes.

Dropbox - number_adjuster_1_2cell.jsx

[Jwalex86]

The Final code I used. It works decent as long as there are not any special characters with the numbers.

Thank you Liphou​ ! My coding is not as nice as Liphou but it works.

var mesArticles = app.activeDocument.stories;
for (var i=0; i < mesArticles.length; i++) { // for 01
        for ( var j= 0; j < mesArticles.tables.length; j++) { // for 02
            for ( var k=0; k < mesArticles.tables.cells.length; k++) { // for 03
                 try {
  if (mesArticles.tables.cells.fillColor.name == 'Cell Red') {
  mesArticles.tables.cells.contents =  tester(mesArticles.tables.cells.contents);  
                  }  // end if
                } catch(e) {
                 // no Color      
               }
              } // end for 03 */ // pour trèté une celure
         }// end for 02
    } // end for 01
function tester(numbers) {
    // $.writeln('tester : ' + numbers.slice(0,1));
     var numberTmp;
     var justin;
     var length = numbers.length;
     justin = "";
           for (l=0; l<numbers.length; l++){
               if (numbers[length-1] == "*")
               {numbers = numbers.substring(0, length - 1);
                   justin = justin + "*";
                   length = length - 1;
                   }
               }
         if (numbers[0] == '$') {
                   numberTmp = numbers.slice(1);
                   numbers = '$'+Calcul(Number(numberTmp)) + justin;
               } else {
                   numbers = Calcul(Number(numbers)) + justin;
               }
    return numbers;
}
//
function Calcul (leNumber) {
    var lePourcentage = Number('1.017');
    var tmp;
    var tmp2;
    if (leNumber < 0){
        tmp2 = Math.abs(leNumber);
        tmp = String(parseInt((tmp2*lePourcentage)+Number('.5')));
        tmp = - tmp;
        }else{
            tmp = String(parseInt((leNumber*lePourcentage)+Number('.5')));
            }
    return (tmp);
}//

[Laubender]

Jwalex86  wrote

… I'm not sure how Liphou  posted the code or I would post what I have.

Hi,

you have to use the "Use advanced editor" control at top right of the forum editor.

Copy/paste your code, select it and use >> "Insert" > "Syntax Highlighting" > "javascript" to format the code.

You have to scroll a bit down to see all options with "Syntax Highlighting":

Regards,
Uwe