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

[johnnylundy]

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:

About R1C1 references
In R1C1 reference style, Microsoft Excel indicates the location of a cell with an “R” followed
by a row number and a “C” followed by a column number. For example, the absolute cell
reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style. If the
active cell is A1, the relative cell reference R[1]C[1] refers to the cell one row down and one
column to the right, or B2.
The following are examples of references in R1C1 style.
Reference Meaning
R[-2]C A relative reference to the cell two rows up and in the
same column
R[2]C[2] A relative reference to the cell two rows down and two
columns to the right
R2C2 An absolute reference to the cell in the second row and in
the second column
R[-1] A relative reference to the entire row above the active cell
R An absolute reference to the current row

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

=SUM(R[-1]C[2]:RC[4])

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).

[johnnylundy]

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…”

Cheers,

Johnny

[johnnylundy]

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

Command: convert formula
Converts cell references in a formula between the A1 and R1C1 reference styles between relative
and absolute references, or both.
Syntax
convert formula
formula to convert Unicode text Required. A string that contains the formula you want to
convert. This must be a valid formula, and it must begin with an equal sign.
from reference style enumeration Required. The reference style of the formula. Can be A1 or R1C1.
[to reference style enumeration] Optional. The reference style you want returned. Can be A1 or R1C1. If
this argument is omitted, the reference style isn’t changed; the formula stays in the style specified by
from reference style.
[to absolute enumeration] Optional. Specifies the converted reference type. Can be A1 or R1C1. If this
argument is omitted, the reference type isn’t changed.
[relative to range] Optional. A range object that contains one cell. Relative references relate to this cell. Can
be a range, an A1-style range reference, a named range, or a list of category names.
Example
This example converts a SUM formula that contains R1C1-style references to an equivalent formula
that contains A1-style references, and then it displays the result.
set inputFormula to “=SUM(R10C2:R15C2)”
display dialog (convert formula formula to convert inputFormula ¬
from reference style R1C1 to reference style A1)

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
	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, 

Johnny

[johnnylundy]

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

Working fine now, and much shorter without the workarounds.

Never mind

[macmadness86]

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

[johnnylundy]

@macmadness86

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.