Relative References in Range BROKEN in Excel v.X? STUMPED

Hey everyone,

After working on this for 8 hours I thought I would see if I am crazy or not.

The Help for MS Excel says about relative references:

OK, so I thought fine, I need to select a range (one row up and 2 columns over) through (same row and 4 columns over).

So I code

tell application "Microsoft Excel"
	Select Cell "R9C1" -- the starting cell for reference (this works fine)
	set x to Range "R[-1]C[2]:RC[4]" --should give a 3 column by 2 row selection.
end tell

The range that gets selected, however, is “C3:C5”, which doesn’t even begin to explain what is wrong:
x -->Range “C3:C5” of Worksheet “Real Estate Sheet” of Workbook “TIAA Real Estate.xls”

To test the syntax, when I go to the Excel sheet and enter the “Sum” icon in the starting cell “R9C1” and then drag the 3-column by 2-row area that I want to specify in the script for selection, the formula bar says


Which is exactly what I coded; the relative references are correct in relation to the starting cell. And it computes the sum properly.

Are relative references busted in AppleScript for Excel v.X for Mac? I can probably figure out an alternate way of selecting this 2x3 block, but I wanted to know if anyone knows 1) is this a known bug? 2) is my AppleScript code somehow not specifying the selection correctly (i.e. is there a “select relative” command or something)?

Appreciate any help on this - it is the next-to-last thing to code to get this working (except finding out if a particular worksheet is open - I will ask about that once this is resolved - right now I worked around it by closing the sheet I want and re-opening it).

Thanks so much Jacques, it works perfectly. You should have seen the subroutine I built to convert relative to absolute. I just deleted it… LOL

Also, the PDF I was not aware, it will be a good read!!! I hope to discover why I can’t find out what worksheets are open, and why "save worksheet “xxxxx” pops up a dialog box “There is already a worksheet with this name…”



Hello again,

Well, I got the PDF Jacques mentioned, and I also picked up a copy of Excel 2004. The 2004 AppleScript fixed the problems with opening and saving the workbook, but now the use of the “Convert Formula” command has changed, and in 10 hours of trying everything I cannot seem to do a simple calculation of one cell or range which is offset from another.

I don’t even have any code to post - nothing works with the Convert Formula command. The PDF describes it as

Of course, as usual, the example they give is ONLY for converting between R1C1 and A1 formats, not what I am interested in, and the example also uses only hard-coded cell references, also not what I have (I have programmatically determined cell references from the “Find” command).

This Find command easily finds the first blank cell in the named range “Date_Price_List”, in this snippet of code:

tell application "Microsoft Excel"
	activate object workbook "TIAA Real Estate.xls"
	activate object sheet "Real Estate Sheet"
	set firstBlank to (find range "Date_Price_List" what "")  -->> range "'[TIAA Real Estate.xls]Real Estate Sheet'!$A$15"
end tell

However, I now need to do two things which were easy before, but now seem to be out of reach:

1) Find the cell which is one row above the cell found by the Find command (in this example it would be $A$14, but it changes with each run of the script) - in other words, given a range theRange "RrCc" which is one cell, how to refer to the cell R(r-1)C.
2) The old problem from yesterday: select the 2 row by 3 column range that is offset from the cell found by the Find command by R[-1]C[2]:RC[4]. In other words a 2 row by 3 column range whose upper left cell is one row up and 2 columns over from the cell "firstBlank" found by the Find command.

I swear, I have tried every permutation of the Convert Formula I can find, and I just can't get it to convert.

If anyone can help me get this right I would be very grateful. 

Thanks to all in advance, 


Heh - I finally figured it out. What a case of crappy documentation.

Working fine now, and much shorter without the workarounds.

Never mind :slight_smile:

Could you post your solution?
What a disappointment after reading through all these posts to find out that you didn’t post the solution!

It’s 8 years later!

I’ve long ago trashed Excel 4 since it won’t run on Intel, or some reason; and the new Excel at first had eliminated AppleScript support, so I can’t open the script in Excel any longer. I switched to Numbers and was going to translate the code, but then APPLE stopped supporting AppleScript in Numbers (at first), so I said screw it.

I can only take screen shots of the code. Unfortunately, I don’t see where MacScripter allows upload of images. So I will manually type in one of the lines:

set lastDatePosted to value of range (convert formula formula to convert "R[-1]C" from reference style R1C1 to reference style A1)

select range (convert formula formula to convert "R[-1]C[2]:RC[8]" from reference style R1C1 to reference style A1)

Since it has been 8 years, I have no idea why this worked.