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

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

I want to reference the cell in master directly, regardless of th
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 th
rider file my cell was at A16 and in the master I actually want AE
for example. so it goes back 8 rows

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

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

P.S. the value of -8 actualy is calculated form a formula. I put -8 i
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

Pab

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