9 Replies Latest reply on Mar 24, 2017 10:26 AM by Jwalex86

    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

       

      Sample table.PNG

        • 1. Re: Increase a number based on cell style
          Liphou Level 1

          Hello

          Should values be rounded?

          Thank you

           

          Bonjour

          Faut-il arrondir les valeurs?

          Merci

          • 2. Re: Increase a number based on cell style
            Liphou Level 1

            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[i].tables.length; j++) { // for 02
                        for ( var k=0; k < mesArticles[i].tables[j].cells.length; k++) { // for 03
                             try {
                                 if (mesArticles[i].tables[j].cells[k].fillColor.colorValue.join(',') == '255,109,109') {
                                    mesArticles[i].tables[j].cells[k].contents =  tester(mesArticles[i].tables[j].cells[k].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

            1 person found this helpful
            • 3. Re: Increase a number based on cell style
              Jwalex86 Level 1

              Thank you!

              • 4. Re: Increase a number based on cell style
                Jwalex86 Level 1

                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.

                • 5. Re: Increase a number based on cell style
                  Liphou Level 1

                  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

                  • 6. Re: Increase a number based on cell style
                    Jwalex86 Level 1

                    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?

                    • 7. Re: Increase a number based on cell style
                      Jwalex86 Level 1

                      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

                      • 8. Re: Increase a number based on cell style
                        Laubender Adobe Community Professional & MVP

                        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":

                         

                        UsingSyntaxHighlighting-AdvancedEditing.png

                         

                        Regards,
                        Uwe

                        1 person found this helpful
                        • 9. Re: Increase a number based on cell style
                          Jwalex86 Level 1

                          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[i].tables.length; j++) { // for 02
                                      for ( var k=0; k < mesArticles[i].tables[j].cells.length; k++) { // for 03
                                           try {
                            if (mesArticles[i].tables[j].cells[k].fillColor.name == 'Cell Red') {
                            mesArticles[i].tables[j].cells[k].contents =  tester(mesArticles[i].tables[j].cells[k].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);
                          }//