Thursday, July 9, 2020

#1 2020-01-16 07:01:48 pm

vw-as
Member
Registered: 2015-08-25
Posts: 11

Numbers - how to put the same value in a specified number of cells

I am (still) a beginner in AppleScript. Numbers version: 6.2.1. macOS 10.15.2.

There are a certain amount of Numbers documents I will be opening, all of which will have one table in them, most with no more than 10 rows.

I would like to put the same value in a number of cells, all of which appear in the same column (eg, the same value entered in to E2, E3, E4, E5 in one Numbers document, while in another it might be E2, E3, E4, E5, E6, E7).

I know how to use the prompt command to set a value to a variable, eg

Applescript:

set NumberOfCells to the text returned of (display dialog "What is the number of cells: " default answer "5")
set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")

And I get put an individual value into an individual cell, eg

Applescript:

set value of cell "A1" to CellValue

But I cannot workout how to set up / use the repeat command to put the same value in a range of cells which I specify.

Help with this would be gratefully received.

Offline

 

#2 2020-01-17 03:24:28 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5239

Re: Numbers - how to put the same value in a specified number of cells

You're spoilt for choice. You can do your own repeat something like this:

Applescript:

-- Set these values from your input.
set theNumber to 22
set theColumn to "E"
set startRow to 2
set endRow to 7

-- Repeat loop:
tell application "Numbers"
   tell document 1
       tell table 1 of sheet 1
           repeat with i from startRow to endRow
               set value of cell i of column theColumn to theNumber
           end repeat
       end tell
   end tell
end tell

Or with an AppleScript-style range specifier:

Applescript:

-- Set these values from your input.
set theNumber to 22
set theColumn to "E"
set startRow to 2
set endRow to 7

-- AppleScript range specifier:
tell application "Numbers"
   tell document 1
       tell table 1 of sheet 1
           set value of cells startRow thru endRow of column theColumn to theNumber
       end tell
   end tell
end tell

Or with one of Numbers's own 'range' specifiers:

Applescript:

-- Set these values from your input.
set theNumber to 22
set theRange to "E2:E7"

-- Cells of a Numbers 'range':
tell application "Numbers"
   tell document 1
       tell table 1 of sheet 1
           set value of cells of range theRange to theNumber
       end tell
   end tell
end tell

The latter two should be slightly faster than the repeat.


NG

Offline

 

#3 2020-01-17 07:06:52 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4544

Re: Numbers - how to put the same value in a specified number of cells

Here are three proposals:

Applescript:

repeat
   set NumberOfCells to the text returned of (display dialog "What is the number of cells (4 or 6): " default answer 4)
   if NumberOfCells is in {"4", "6"} then exit repeat
   beep
end repeat

set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set firstRow to 2
set lastRow to firstRow - 1 + (NumberOfCells as integer)
set theRange to "E" & firstRow & ":E" & lastRow

tell application "Numbers"
   tell document 1 to tell table 1 of sheet 1
       set value of cells of range theRange to CellValue
   end tell
end tell

Applescript:

set cancelBtn to "Cancel"
set btn4 to "4"
set btn6 to "6"
set NumberOfCells to button returned of (display dialog "What is the number of cells to fill: " buttons {cancelBtn, btn6, btn4} default button btn4 cancel button cancelBtn)

set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set firstRow to 2
set lastRow to firstRow - 1 + (NumberOfCells as integer)
set theRange to "E" & firstRow & ":E" & lastRow

tell application "Numbers"
   tell document 1 to tell table 1 of sheet 1
       set value of cells of range theRange to CellValue
   end tell
end tell

Applescript:

set cancelBtn to "Cancel"
set btn5 to "5"
set btn7 to "7"
set lastRowToFill to button returned of (display dialog "What is the index of the last row to fill: " buttons {cancelBtn, btn7, btn5} default button btn5 cancel button cancelBtn)

set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")
set theRange to "E2:E" & lastRowToFill

tell application "Numbers"
   tell document 1 to tell table 1 of sheet 1
       set value of cells of range theRange to CellValue
   end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 17 janvier 2020  14:06:20

Offline

 

#4 2020-01-17 12:08:42 pm

vw-as
Member
Registered: 2015-08-25
Posts: 11

Re: Numbers - how to put the same value in a specified number of cells

Nigel, Yvan,

Thank you both for the very detailed and very helpful replies.

Victor

Offline

 

#5 2020-01-17 03:39:01 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4544

Re: Numbers - how to put the same value in a specified number of cells

As I am lazy, here is a version which spare user's activity.
There is no need to enter the index of the lower cell to fill.
It's supposed to be stored in the cell "E1".

Applescript:

set CellValue to the text returned of (display dialog "What value would you like to put in the cells? " default answer "X")

tell application "Numbers"
   tell document 1 to tell table 1 of sheet 1
       -- The index of the lower cell to fill is stored in E1"
       set lastRowToFill to value of cell "E1" as integer
       set theRange to "E2:E" & lastRowToFill
       set value of cells of range theRange to CellValue
   end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) vendredi 17 janvier 2020  22:38:35

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)