Accessing cells with offset reference in Numbers with Applescript

Question

How can I reference a cell relative to another cell in Applescript like in Excel VBA? In Excel VBA I could use "offset" to set the value of cell D2:

Range("A1").Offset(1,3).Value = "Example"

I searched everywhere but there doesn't seem to be an "offset"-command in Numbers Applescript, despite it's so super handy.

Any help is much appreciated!

Answer 1

Edit: Thanks to user3439894 for pointing out the problems with my first answer - it was totally wrong, based on Excel instead of Numbers.

Here's a full script example, showing one method to achieve the objective in Numbers:

tell application "Numbers"
    tell table 1 of sheet 1 of document 1
        set c to cell "A1"'s column's address as number
        set r to cell "A1"'s row's address as number
        set value of cell (c + 3) of row (r + 1) to "Example"
    end tell
end tell

You could condense the set command into one line, like this:

tell application "Numbers"
    tell table 1 of sheet 1 of document 1
        set value of cell ((cell "A1"'s column's address as number) + 3) of row ((cell "A1"'s row's address as number) + 1) to "Example"
    end tell
end tell

Answer 2

To complement the excellent answer by David...

If you have a need to mimic the VBA expression, the example code from David's answer can be rolled into a handler, as in the example below.

on setCellOffsetValue(cl, co, ro, val)
    tell application "Numbers"
        tell table 1 of sheet 1 of document 1
            set c to cell cl's column's address as number
            set r to cell cl's row's address as number
            set value of cell (c + co) of row (r + ro) to val
        end tell
    end tell
end setCellOffsetValue

Now you can use it multiple times in the same script by calling it, e.g.:

setCellOffsetValue("A1", 3, 1, "Example")

As you can see in this version, the setCellOffsetValue handler takes four parameters:

• cl - The cell to offset from.
• co - The column offset from the cell.
• ro - The row offset from the cell).
• val - The value of the offset cell.

Place the setCellOffsetValue handler within your script and call it as needed.

---

The handler above has table, sheet, and document hardcoded, each to 1. However, in this example you also pass that information to the handler:

on setCellOffsetValue(cl, co, ro, val, t, s, d)
    tell application "Numbers"
        tell table t of sheet s of document d
            set c to cell cl's column's address as number
            set r to cell cl's row's address as number
            set value of cell (c + co) of row (r + ro) to val
        end tell
    end tell
end setCellOffsetValue

Now you can use it multiple times in the same script by calling it, e.g.:

setCellOffsetValue("A1", 3, 1, "Example", 1, 1, 1)

Or:

setCellOffsetValue("A1", 3, 1, "Example", "Table 1", "Sheet 1", "Untitled")

The last three parameters can be either their numeric value or name value, as appropriate for the need at that moment.

This version will be handy for documents that have multiple tables and or sheets and the need to target other then table 1 of sheet 1 of document 1.

As you can see in this version, the setCellOffsetValue handler takes seven parameters:

• cl - The cell to offset from.
• co - The column offset from the cell.
• ro - The row offset from the cell).
• val - The value of the offset cell.
• t - The table number or name.
• s - The sheet number or name.
• d - The document number or name.

---

Note: The example AppleScript code is just that and does not contain any error handling as may be appropriate. The onus is upon the user to add any error handling as may be appropriate, needed or wanted. Have a look at the try statement and error statement in the AppleScript Language Guide. See also Working with Errors.