Skip navigation

reset cells

805 Views 18 Replies Latest reply: Nov 28, 2012 2:11 AM by Barry RSS
1 2 Previous Next
ardelas Calculating status...
Currently Being Moderated
Sep 11, 2011 8:54 AM

Who I can reset the content of a group of cells? (put all cells in 0 or nothing) (not manually, of course)

thanks

iMac, Mac OS X (10.6.8)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 11, 2011 9:31 AM (in response to ardelas)

    Select the group. Press delete.

     

    • To select a contiguous group, click in the cell at one corner of the group to select it, click again and drag to the opposite corner of the group to add to the selection.
      OR
    • Click in the cell at one corner of the group to select it; shift-click in the cell at the opposite corner of the group to add all cells between to the selection.

     

    • To add non-contiguous cells to a selection, command-click on each.

     

    • To add a second group of contiguous cells, command-click and drag from one corner of this group to the opposite corner.

     

    If the cells to be cleared all have the same content (and are the only cells with that content), use Find and Replace.

     

    If this is a task to be done repeatedly with the same set of cells, it's likely possible to set up an Applescript to perform the task.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 11, 2011 11:18 AM (in response to ardelas)

    Cells can contain a formula, which determines the value displayed. The formula could be set to display a zero value when a checkbox cell is clicked, and a different value when it is clicked again (unchecked). That's not what you're looking for.

     

    Cells can contain an entered value.

     

    Cells can't contain both. If you enter a value (or delete a value) that value replaces the formula that was in the cell.

     

    As mentioned in my earlier reply, if the cells to which this will apply are known it should be possible to write an AppleScript to set those cells to zero or to 'empty'. The script will be a menu item in the Scripts menu though, not triggered by a button. Numbers does not provide support for buttons that run a script.

     

    Scripts are Yvan's department (and that of a few other, more casual, visitors to this community). One or more of them will likely respond later.

     

    Regards,

    Barry

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Sep 11, 2011 1:29 PM (in response to ardelas)

    Here is a script which may help.

     

    --{code}

    --[SCRIPT zero_in_cells]

    (*

    Enregistrer le script en tant que Script : zero_in_cells.scpt

    déplacer le fichier ainsi créé dans le dossier

    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:

    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

     

    Sélectionner un groupe de cellules.

    Aller au menu Scripts , choisir Numbers puis choisir “zero_in_cells”

    Le script placera le nombre zéro dans toutes les cellules du groupe.

     

    --=====

     

    L’aide du Finder explique:

    L’Utilitaire AppleScript permet d’activer le Menu des scripts :

    Ouvrez l’Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

    Cochez la case “Afficher le menu des scripts dans la barre de menus”.

    Sous 10.6.x,

    aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript

    puis cocher la case “Afficher le menu des scripts dans la barre des menus”.

     

    --=====

     

    Save the script as a Script: zero_in_cells.scpt

     

    Move the newly created file into the folder:

    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

     

    Select a block of cells.

    Go to the Scripts Menu, choose Numbers, then choose “zero_in_cells”

    The script will fill every selected cells with the number zero.

     

    --=====

     

    The Finder’s Help explains:

    To make the Script menu appear:

    Open the AppleScript utility located in Applications/AppleScript.

    Select the “Show Script Menu in menu bar” checkbox.

    Under 10.6.x,

    go to the General panel of AppleScript Editor’s Preferences dialog box

    and check the “Show Script menu in menu bar” option.

     

    --=====

     

    Yvan KOENIG (VALLAURIS, France)

    2011/09/11

    *)

    --=====

     

    on run

              local dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2

              set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

              tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

                        set value of every cell of range (name of cell rowNum1 of column colNum1 & ":" & name of cell rowNum2 of column colNum2) to 0

              end tell

    end run

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

    *)

    on get_SelParams()

              local d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2

              tell application "Numbers" to tell document 1

                        set d_name to its name

                        set s_name to ""

                        repeat with i from 1 to the count of sheets

                                  tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

                                  if maybe is not 0 then

                                            set s_name to name of sheet i

                                            exit repeat

                                  end if -- maybe is not 0

                        end repeat

                        if s_name is "" then

                                  if my parleAnglais() then

                                            error "No sheet has a selected table embedding at least one selected cell !"

                                  else

                                            error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

                                  end if

                        end if

                        tell sheet s_name to tell (first table where selection range is not missing value)

                                  tell selection range

                                            set {top_left, bottom_right} to {name of first cell, name of last cell}

                                  end tell

                                  set t_name to its name

                                  tell cell top_left to set {row_num1, col_num1} to {address of its row, address of its column}

                                  if top_left is bottom_right then

                                            set {row_num2, col_num2} to {row_num1, col_num1}

                                  else

                                            tell cell bottom_right to set {row_num2, col_num2} to {address of its row, address of its column}

                                  end if

                        end tell -- sheet…

                        return {d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2}

              end tell -- Numbers

    end get_SelParams

     

    --=====

     

    on parleAnglais()

              local z

              try

                        tell application "Numbers" to set z to localized string "Cancel"

              on error

                        set z to "Cancel"

              end try

              return (z is not "Annuler")

    end parleAnglais

     

    --=====

     

    on decoupe(t, d)

              local oTIDs, l

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d

              set l to text items of t

              set AppleScript's text item delimiters to oTIDs

              return l

    end decoupe

     

    --=====

    --[/SCRIPT]

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) dimanche 11 septembre 2011 22:28:40

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community


  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Sep 11, 2011 2:09 PM (in response to ardelas)

    You select a range of cells then run the script.

    It will fill the selected cells with Zeroes.

     

    Isn't it what you wanted ?

     

    I guess that you use Numbers without knowing how it work.

    Do the same with the script.

     

    Yvan KOENIG (VALLAURIS, France) dimanche 11 septembre 2011 23:09:06

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 11, 2011 4:30 PM (in response to ardelas)

    Hi ardelas,

     

    In order to make a change a set of cells, the script needs to know which cells are in the set. As you haven't provided that information, or any information on how the script could determine which cells are to be changed, Yvan used the basic method: Select the cells; perform the action. The selected cells must be in a single contiguous block (in the current version of the script).

     

    The script can be revised to act on a fixed set of cells, or to learn by some other method which cells are included.

     

    But at the moment, you're the only one who knows which cells those are.

     

    So, the question is: Which specific cells are to be reset?

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 12, 2011 12:56 AM (in response to ardelas)

    ardelas wrote:

    When I click something...put to 0 cells b3:g8 for exemple.

    I need to avoid human error

    Does 'for example' indicate it won't always be B3:G8? If not, how will the script know which cells to reset?

     

    Is this a single sheet document with only one table? Is the Sheet named "Sheet 1"? Is the Table named "Table 1"?

     

    Scripts are quite literal in the instructions they require. All of these details are necessary.

     

    I'd suggest avoiding "for example," and telling Yvan precisely what you need. Include the document name, sheet name, table name and range of cells to be set to zero. If there's more than one block of cells to be changed, include that information as well.

     

    I was able to read the script well enough to make a simple change that made it set to zero the cells you specified without having to select them. With all the information, Yvan will be able to provide a cleaner script that does the same thing. But, as he's said on previous occasions, he's not standing behind you, looking over your shoulder, and his crystal ball isn't working this week. The only way he'll have the information to build the script you need is if you provide it.

     

    Regards,

    Barry

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Sep 12, 2011 1:49 AM (in response to ardelas)

    I'm not a sooth sayer so my answer was built upon available infos.

    If you want an other way to define the range of cells to zero, describe it.

    Barry described perfectly what is needed.

     

    (1) document's name

    (2) sheet's name

    (3) table's name

    (4) range of cells to zero.

     

    I highlighted the word name for sheet and table because we can't work reliably with items index.

     

    If the range to zero is always the same, edit the instruction

     

    Only this run handler would be required

     

     

    --{code)

    on run

              tell application "Numbers" to tell document "document's name" to tell sheet "sheet's name" to tell table "table's name"

                        set value of every cell of range "B12:G20" to 0

              end tell

    end run

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) lundi 12 septembre 2011 10:48:53

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

  • MySoubriquet Calculating status...
    Currently Being Moderated
    Nov 23, 2011 12:16 PM (in response to KOENIG Yvan)

    If someone did need more than one range of cells or even a group of non-contiguous cells to be set to 0, how would it be done?  I've tried:

     

    --{code)

    on run

              tell application "Numbers" to tell document "Costs Per Show" to tell sheet "Calculator" to tell table "Calculator"

                        set value of cell ({"B9", "C2", "C12", "D9", "E2", "E12", "F9", "G2", "G12", "H9", "I2", "I12", "B9", "C2", "C12", "D9", "E2", "E12", "F9", "G2", "G12", "H9", "I2", "I12"}) to 0

              end tell

    end run

    --{code}

     

     

    Also, would that bit be the complete script? Or did you just mean for it to replace and instruction part of the longer script you posted earlier?

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Nov 23, 2011 12:44 PM (in response to MySoubriquet)

    We can't define the value of cells with the syntax which you tried to use.

     

    Use:

    --{code)

    on run

              tell application "Numbers" to tell document "Costs Per Show" to tell sheet "Calculator" to tell table "Calculator"

                        repeat with cellRef in {"B9", "C2", "C12", "D9", "E2", "E12", "F9", "G2", "G12", "H9", "I2", "I12", "B9", "C2", "C12", "D9", "E2", "E12", "F9", "G2", "G12", "H9", "I2", "I12"}

                                  set value of cell cellRef to 0

                        end repeat

              end tell

    end run

    --{code}

     

    Yvan KOENIG (VALLAURIS, France)  mercredi 23 janvier 2011 21:44:00

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

     

     

  • MySoubriquet Level 1 Level 1 (0 points)
    Currently Being Moderated
    Nov 23, 2011 1:12 PM (in response to KOENIG Yvan)

    When I run it I get an error: Numbers got an error: Can't set document "Costs Per Show" to 0.

     

    UPDATE: I had to add ".numbers" to the Document name. The script starts to work now, meaning it changes the value of the first cell B9 to 0, but then stops. No errors are given. It doesn't seem to be taking the repeat command seriously.

     

    Thank you very very much for your help!! I really appreciate how amazingly helpful you are on these boards.

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.