Make
R[-8] as R8 and it will be absolute. You should probably remove the [
and ] from C[" & 26 + counter & "],


--
Regards,
Tom Ogilvy

pabs wrote in message
...
I have a macro that opens a file (rider.xls) and looks up some values in
another file (master.xls) and dumps the result in the first file
(rider.xls)

seems that lookup is using my current active cell position (from the
rider.xls file) to offset itself in the master file.

I want to reference the cell in master directly, regardless of the
position in the rider.xls file.

here is one of the formulas.

ActiveCell.FormulaR1C1 = _
"=INDEX([Master.xls]defenition!R6C1:R80C5,
MATCH([Master.xls]Master1!*R[-8]* C[" & 26 + counter & "],
[Master.xls]defenition!R6C1:R130C1,),
MATCH(R15C5,[Master.xls]defenition!R6C1:R6C5,))"


My problem comes from the R[-8]. it's negative 8 because in the
rider file my cell was at A16 and in the master I actually want AE8
for example. so it goes back 8 rows

can't I simply reference to the cell in master.xls directly, not
relative to my other file??

I don't understand why that reference is relative and the other ones
aren't (i.e. R6C1) in the same formula

P.S. the value of -8 actualy is calculated form a formula. I put -8 in
the example so that you see what the first one would look like.
The first time it;s -8 but it then it cahnges depending on the lookup


thanks

Pabs


---
Message posted from http://www.ExcelForum.com/