relative position in macro

  • Thread starter Thread starter pabs
  • Start date Start date
P

pabs

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 :confused:

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
 
Make
R[-8] as R8 and it will be absolute. You should probably remove the [
and ] from C[" & 26 + counter & "],
 
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
 
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.
 
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
 
Back
Top