Benches Uncategorized

2

find and replace on empty cells

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Is there a way to find and replace all empty cells in a table?

When I leave the find value blank, the replace option is greyed out.

Thanks,

2 replies

Sign in or join Beachball Help to reply.

  1. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    As far as I know, the response is no.

    It's why I posted some months ago a script designed to fill blank cells.

    Here is a slightly revised version :

    --


    --[SCRIPT fillBlanks]
    (*
    Enregistrer le script en tant que Script : fillBlanks.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 une plage de cellules à remplir..
    aller au menu Scripts , choisir Numbers puis choisir fillBlanks
    Les cellules vides de la sélection seront remplie avec la chaîne saisie.
    --=====
    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".
    --=====
    Save the script as a Script : fillBlanks.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 range of cells to fill.
    go to the Scripts Menu, choose Numbers, then choose fillBlanks
    The script will fill every blank cell with the given string .
    --=====
    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.
    --=====
    Yvan KOENIG (VALLAURIS, France)
    2009/02/06
    2010/06/11
    *)
    --=====
    property t_Name : "testIfEmpty"
    property ISBLANK_loc : ""
    --=====
    on run
    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} ¬
    to my getSelParams()
    (* Here we know the starting point of the destination area. *)
    tell application (path to frontmost application as string)
    if my parleAnglais() then
    set msg to text returned of (display dialog ¬
    "Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
    else
    set msg to text returned of (display dialog ¬
    "Enter the string which will fill the empty cells" default answer "filled")
    end if
    end tell

    my initBlankChecker(dName, sName)

    repeat with r from rowNum1 to rowNum2
    repeat with c from colNum1 to colNum2
    if my isItBlank(dName, sName, tName, r, c, sName) then
    tell application "Numbers" to tell document dName
    tell sheet sName to tell table tName
    set value of cell r of column c to msg
    end tell -- sheet …
    end tell -- Numbers …
    end if -- my isItBlank(…
    end repeat -- c
    end repeat -- r

    tell application "Numbers" to tell document dName to tell sheet sName
    delete table t_Name
    end tell
    end run
    --=====
    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to ¬
    my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to ¬
    my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
    end if
    return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
    end getSelParams
    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
    apply to named row or named column !
    *)
    on decipher(d, s, t, n)
    tell application "Numbers" to tell document d to tell sheet s to tell table t
    return {address of row of cell n, address of column of cell n}
    end tell
    end decipher
    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to ¬
    my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection
    --=====
    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe
    --=====
    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
    --=====
    (*
    Initialization process required before calling isItBlank.
    Both require :
    property t_name : "testIfEmpty"
    property ISBLANK_loc : ""
    my initBlankChecker(dName,sheetForTheTempTable)
    *)
    on initBlankChecker(doc2test, sheet4test)
    set ISBLANK_loc to my getLocalizedFunctionName("Numbers", "ISBLANK")

    tell application "Numbers" to tell document doc2test to tell sheet sheet4test
    if not (exists table t_Name) then
    make new table with properties {name:t_Name, row count:1, column count:1}
    end if
    set tProps to get properties of table t_Name

    set nbr to (row count of tProps) - 1
    if nbr > 0 then
    repeat nbr times
    remove row 1 of table t_Name
    end repeat
    end if

    set nbr to (column count of tProps) - 1
    if nbr > 0 then
    repeat nbr times
    remove column 1 of table t_Name
    end repeat
    end if
    end tell -- Numbers
    end initBlankChecker
    --=====
    (*
    Check if a cell is Blank and returns status accordingly.
    An initialization process must be done before calling this handler.
    Both require :
    property t_name : "testIfEmpty"
    property ISBLANK_loc : ""
    if my isItBlank(dName, sReport, tAssignments, 1, c,sheetForTheTempTable) then exit repeat
    *)
    on isItBlank(doc2test, sheet2test, table2test, row2test, col2test, sheet4test)
    local status
    tell application "Numbers" to tell document doc2test
    set sheet2test to (get name of sheet sheet2test)
    tell sheet sheet2test
    set table2test to (get name of table table2test)
    tell table table2test
    set cellRef to (get name of cell row2test of column col2test)
    set status to value of cell cellRef
    end tell -- table table2test
    end tell -- sheet sheet2test
    if status is not 0.0 then
    set status to false
    else
    set sheet4test to (get name of sheet sheet4test)
    tell sheet sheet4test to tell table t_Name
    set value of cell "A1" to "=" & ISBLANK_loc & ¬
    "('" & sheet2test & "' :: '" & table2test & "' :: " & cellRef & ")"
    set status to value of cell "A1"
    end tell
    end if -- flag
    end tell
    return status
    end isItBlank
    --=====
    on get_iWorkNum(a)
    local verNum
    tell application a to set verNum to item 1 of my decoupe(get version, ".")
    if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
    return "09"
    else
    return "11"
    end if
    end get_iWorkNum
    --=====
    (*
    Useful to get function's localized name if we need to build formulas
    examples:
    set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
    set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
    set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
    *)
    on getLocalizedFunctionName(theApp, x)
    return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & ¬
    my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
    end getLocalizedFunctionName
    --=====
    on getLocalizedName(a, x, f)
    tell application a to return localized string x from table "Localizable" in bundle file f
    end getLocalizedName
    --=====
    --[/SCRIPT]
    --

    Yvan KOENIG (VALLAURIS, France) vendredi 11 juin 2010 17:52:59

  2. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Oops

    Please, edit the block :

    --


    if my parleAnglais() then
    set msg to text returned of (display dialog ¬
    "Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
    else
    set msg to text returned of (display dialog ¬
    "Enter the string which will fill the empty cells" default answer "filled")
    end if
    --

    as

    --


    if my parleAnglais() then
    set msg to text returned of (display dialog ¬
    "Enter the string which will fill the empty cells" default answer "filled")
    else
    set msg to text returned of (display dialog ¬
    "Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
    end if
    --

    Yvan KOENIG (VALLAURIS, France) vendredi 11 juin 2010 18:18:17