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

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/

I can't do that since I actually pass a formula.. I simply put -8 to
show the value it was returning

R["&someFormula &"] and teh Result of someFormula has to be -8 in order
for it to work.. because it's relative to my Rider.xls" file.

I just want to be able to reference it directly to teh MAster.xls file
so that my formula would simply return the exact row location in the
MAster file ..


Pabs


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

You seem to be missing the point.

R" & someformula & "

rather than with the brackets if you want the reference to be absolute. If
someformula returns a value between 1 and 65536 inclusive, then it is a
valid expression.

--
Regards,
Tom Ogilvy

pabs wrote in message
...
I can't do that since I actually pass a formula.. I simply put -8 to
show the value it was returning

R["&someFormula &"] and teh Result of someFormula has to be -8 in order
for it to work.. because it's relative to my Rider.xls" file.

I just want to be able to reference it directly to teh MAster.xls file
so that my formula would simply return the exact row location in the
MAster file ..


Pabs


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

hehe... stupid mistake on my part..I tried what you had said the firs
time and I was getting errors...I thought the syntax was off

turns out my formula was still passing a negative number..that's why i
didn't work..

works fine now :)

thanks TO

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