Re: Value of an empty cell in Numbers
Re: Value of an empty cell in Numbers
- Subject: Re: Value of an empty cell in Numbers
- From: KOENIG Yvan <email@hidden>
- Date: Wed, 4 Feb 2009 10:53:13 +0100
On 4 févr. 2009, at 03:23:50, Deivy Marck Petrescu wrote:
Since there are problems with handling dates in Numbers, we should
mention problems with empty cells in numbers.
Say in your table you set the cell "B2" to 0 and leave the cell
"C2" empty.
Then you run the following script:
----
tell application "Numbers"
tell document 1 to tell sheet 1 to tell table 1
set Range1 to value of cell "B2"
set Range2 to value of cell "C2"
return {class of Range1, class of Range2, Range1 = Range2,
Range1, Range2}
end tell
end tell
--->{real, real, true, 0.0, 0.0}
That is, an empty cell has value 0 as real , and it says that its
value is the same as the cell whose values *is* 0.
The problem is already reported.
Here is my workaround:
--[SCRIPT isItBlank]
(*
Enregistrer le script en tant qu'Application ou Progiciel :
isItBlank.app
déplacer l'application créée dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Nu
mbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être
même le dossier Applications.
Sélectionnez la cellule à tester
menu Scripts > Numbers > isItBlank
Le script crée une table temporaire dans laquelle il insère
une formule du type =ESTVIDE(feuille 1 :: tableau 1 :: H12)
et retourne la valeur calculée par cette formule.
+++++++++
Save the script as an Application or an Application Bundle:
isItBlank.app
Move the newly created application 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 the cell to test.
menu Scripts > Numbers > isItBlank
The script creates a temporary sheet in which it inserts
a formula like =ISBLANK(sheet 1 :: table 1 :: H12)
and returns the value calculated by this formula.
Yvan KOENIG (Vallauris, FRANCE)
20 janvier 2009
23 janvier 2009 - shorten the used formula
26 janvier - enhance to every sheet/table/cell
*)
property theApp : "Numbers"
property delim : missing value
property ISBLANK_loc : missing value
--=====
on run
if delim is missing value then
if character 2 of (0.5 as text) is "." then
set delim to ","
else
set delim to ";"
end if
set p2lproj to my getLproj(theApp)
set ISBLANK_loc to my getLocalizedFuncName(p2lproj, "ISBLANK")
end if
set {rName, tName, sName, dName} to my getSelection()
if rName is missing value then error "No selected cells"
set twoNames to my decoupe(rName, ":")
set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
(*
if item 2 of twoNames = item 1 of twoNames then
set {colNum2, rowNum2} to {colNum1, rowNum1}
else
set {colNum2, rowNum2} to my decipher(item 2 of twoNames)
end if
*)
(* Here we know the starting point of the destination area. *)
if my parleFrancais() then
if my doYourDuty(rowNum1, colNum1, tName, sName) then
set msg to "est vide."
else
set msg to "n’est pas vide."
end if
display dialog "La cellule" & return & "colonne: " & colNum1 &
return & "de la ligne: " & rowNum1 & return & "du tableau: " & tName
& return & "de la feuille: " & sName & return & "du document: " &
dName & return & msg
else
if my doYourDuty(rowNum1, colNum1, tName, sName) then
set msg to "is blank."
else
set msg to "is not blank."
end if
display dialog "The cell" & return & "column: " & colNum1 & return
& "of row: " & rowNum1 & return & "of table: " & tName & return & "of
sheet: " & sName & return & "of document: " & dName & return & msg
end if
end run
--=====
on doYourDuty(r, c, t, s)
local tName, newTable, errMsg, errNum, sheetName, flag
tell application "Numbers"
tell document 1 to tell sheet 1
set tName to "temporary_yraropmet"
set newTable to make new table with properties {name:tName}
try
newTable as text
on error errMsg number errNum
set sheetName to item 4 of my decoupe(errMsg, quote)
end try
end tell -- sheet 1 of document 1
tell document 1 to tell sheet sheetName
tell table tName (*
assuming that r,c points cell H12, inserts the localized formula
=ISBLANK(H12) *)
set value of cell 1 of row 1 to "=" & ISBLANK_loc & "(" & s &
" :: " & t & " :: " & my refEnLettres(r, c) & ")"
(* grabs the returned value *)
set flag to value of cell 1 of row 1
end tell -- tName
delete table tName (* removes the temporary table *)
end tell -- sheet sheetName of document 1
end tell -- application
return flag
end doYourDuty
--=====
on getSelection()
local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable,
mySheet, myDoc, mySelection
tell application "Numbers"
activate
tell document 1
set mySelectedRanges to selection range of every table of every sheet
repeat with sheetRanges in mySelectedRanges
repeat with thisRange in sheetRanges
if contents of thisRange is not missing value then
try
--return thisRange --poorly formed result
thisRange as text
on error errMsg number errNum
set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my
decoupe(errMsg, quote)
--set mySelection to (a reference to (range rn of table tn of
sheet sn))
return {myRange, myTable, mySheet, myDoc}
end try
end if -- contents…
end repeat -- thisRange
end repeat -- sheetRanges
end tell -- document 1
end tell -- application
return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on decipher(n)
local letters, colNum, rowNum
set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
if (character 2 of n) as text > "9" then
set colNum to (offset of (character 1 of n) in letters) * 64 +
(offset of (character 2 of n) in letters)
set rowNum to (text 3 thru -1 of n) as integer
else
set colNum to offset of (character 1 of n) in letters
set rowNum to (text 2 thru -1 of n) as integer
end if
return {colNum, rowNum}
end decipher
--=====
on refEnLettres(rowNum, colNum)
set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
set seuil to 26
if colNum > seuil then
set n1 to colNum div seuil
set n2 to colNum - (n1 * seuil)
set enLettre to character n1 of letters & character n2 of letters &
rowNum
else
set enLettre to character colNum of letters & rowNum
end if
return enLettre
end refEnLettres
--=====
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 getLocale(a, x)
tell application a to return localized string x
end getLocale
--=====
on getLocalizedFuncName(f, x)
return localized string x from table "Localizable" in bundle file f
end getLocalizedFuncName
--=====
on getLproj(aa)
local lprojName
set lprojName to my getlprojName(aa) & ".lproj"
return (path to application support as text) & "iWork
'09:Frameworks:SFTabular.framework:Versions:A:Resources:" & lprojName
end getLproj
--=====
on getlprojName(aa)
local lprojs, localId, lproj
set lprojs to {{"da_DK", "da"}, {"nl_NL", "Dutch"}, {"en_US",
"English"}, {"fi_FI", "fi"}, {"fr_FR", "French"}, {"de_DE",
"German"}, {"it_IT", "Italian"}, {"ja_JP", "Japanese"}, {"ko_KR",
"ko"}, {"no_NO", "no"}, {"pl_PL", "pl"}, {"pt_BR", "pt"}, {"pt_PT",
"pt_PT"}, {"ru_RU", "ru"}, {"es_ES", "Spanish"}, {"sv_SE", "sv"},
{"zf_CN", "zh_CN"}, {"zh_TW", "zh_TW"}}
if aa starts with "Pages" then (* why are they using different
strings ? *)
set localId to my getLocale(aa, "http://support.apple.com/manuals/
#iwork")
else (* here for Keynote or Numbers *)
set localId to my getLocale(aa, "http://support.apple.com/en_US/
manuals/#iwork")
end if
set localId to text (1 + (count of "http://support.apple.com/";))
thru -1 of localId
set localId to text 1 thru ((offset of "/" in localId) - 1) of localId
set lproj to ""
repeat with i from 1 to count of lprojs
if localId is item 1 of item i of lprojs then
set lproj to item 2 of item i of lprojs
exit repeat
end if
end repeat
if lproj = "" then
if my parleFrancais() then
error "Le fichier FrameWorks " & localId & " manque !"
else
error "The Frameworks file " & localId & "is missing !"
end if
else
return lproj
end if
(* returns
da
Dutch
English
fi
French
German
Italian
Japanese
ko
no
pl
pt
pt_PT
ru
Spanish
sv
zh_CN
zh_TW
given the language used to display the program's GUI. *)
end getlprojName
--=====
on parleFrancais()
local z
try
tell application theApp to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z = "Annuler")
end parleFrancais
--=====
--[/SCRIPT]
Yvan KOENIG (from FRANCE mercredi 4 février 2009 10:52:52) _______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden