Sorry for the duplication Paul, I meant to send to the list.
____________ Begin forwarded message: On Feb 8, 2009, at 3:14 PM, Paul Berkowitz wrote: On 2/8/09 11:44 AM, "Deivy Marck Petrescu" <email@hidden> wrote:
I don't understand what you're working with here. Nor do I understand where the R3 comes from in the first statement of the script. What is "RC[-1]" meant to indicate and how does it end up as "R3" in the first example and as "RC[-1]" in the second example?
I don't know about the newer versions, but in excel X, you can refer to cell "A1" as "A1" or as "R1C1", the latter means "row 1 column 1".
The R1C1 cell-address terminology cannot be used in Excel 2004 or 2008. Only the A1 terminology.
So, to put the value in any cell, one can select either is customary excel name, "B1", Z10" or the location by row and column.
One problem is everything is fine except for cells like "Cn" or "Rn" where n is a number. This, because excel understands those as "Column n" or "Row n".
No longer a problem. It is unambiguous now with the absence of R1C1 addressing. C3 just means the cell "C3".
Now suppose I select cell "A1" and I want to have "B1" as the same value of "A1".
I can write the following,
------> set value of Cell "B1" to "=A1"
This will translate in a B1 cell as =$A$1.
Where the "$" indicates absolute reference. So if I drag down the value of cell "B1" to have the value of "B2" = "A2", I would get instead "B2"="A1".
However, if if instead of the line above I use
------> set value of Cell "B1" to "=RC[1]"
This will translate it to "=A1" as a referencial position, instead of absolute position. That is, dragging down B1 would make "B2" = "A2" and so on.
In other words, RC[1] means: the same row but next column. R[-2]C[2] means: 2 rows before the current row and 2 columns after the current column.
You can't do that any longer, as you'll realize by now. There are other ways of getting relative positions, including the 'get offset' command:
set value of cell "B1" to (get value of (get offset cell "B1" column offset 1))
set value of cell "B1" to (get value of (get offset cell "B1" row offset -2 column offset -2))
Unambiguous. Always works (but note that it needs the explicit 'get' or a variable).
For someone not too familiar with =LOOKUP function, could you please describe what values are actually in T2:U6, what value you're trying to find ("RC[-1]" literally or something else?) and what exactly you would enter in the UI and what result you get, that you're trying to do by script. Thanks.
T2:U6 is a range of cells. The first cell is on "T2", the last in "U6".
This means the range t2,t3,t4,t5,t6, u2,u3,u4,u5,u6. A bicolumnar range with 10 cells.
U2:U6 is a columnar reference to these five cells range.
There is no UI scripting everything is excel scripting.
So, the R3 is right (I entered RC[-1] in S3, same row one column before), I d not understand why the -- ' -- show up. They were not supposed to be there.
I know all that. I'm asking you to give me an example of what you want to do, beginning with your actual values in the T2:U6 range, describing what you'd enter in the UI (not GUI scripting - just regular old Excel UI), what result you end up with, so I can attempt to script it using new Excel scripting in 2008.
--
Paul Berkowitz
Paul, thanks for all the clarification. The good news is my original script will work fine with the newer versions, since I can use the letter of the column and the number of the row!
I do have to say, that in terms of reference I prefer RC[-1] to the newer versions get offset!
As for the values in the ranges, actually, all the values in all the ranges are entered via script (not GUI or UI) and the values had nothing to do with the problem. I found out the reason the problem was happening, but I have no clue as to why it happened. If I use "R3" instead of RC[-1], the problem does not happen. And since in this case I am entering the formula it is fine to enter "R3". I am still baffled as to why would excel put the single quotes in there.
And if it was not clear, I am using excel X here so, all the RC notation works well. Actually sometimes it is the only way it works!
Thank you.
|