addressing type in FormulaR1C1
 

Hi All,

What does it depend on that FormulaR1C1 generates relative or absolute cell
references?
I have a macro with several FormulaR1C1, with different results, e.g.
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
generates =COUNTIF(D:D,D2), that's OK, but

Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R2C[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],R2C[-2]:R" &
noOfNames & "C[-2],0))"
generates =INDEX(F$2:F$6,MATCH(H2,G$2:G$6,0))
Why are $ signs at the row references and how can I influence their presence?
Infact I want to generate this mixed type formula:
=INDEX(F2:F$6,MATCH(H2,G2:G$6,0))
How can I do that?

Thanks,
Stefi

addressing type in FormulaR1C1
 

Sorry, ignore this post! I found the answer!
Stefi


€žStefi€� ezt Ã*rta:

Hi All,

What does it depend on that FormulaR1C1 generates relative or absolute cell
references?
I have a macro with several FormulaR1C1, with different results, e.g.
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
generates =COUNTIF(D:D,D2), that's OK, but

Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R2C[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],R2C[-2]:R" &
noOfNames & "C[-2],0))"
generates =INDEX(F$2:F$6,MATCH(H2,G$2:G$6,0))
Why are $ signs at the row references and how can I influence their presence?
Infact I want to generate this mixed type formula:
=INDEX(F2:F$6,MATCH(H2,G2:G$6,0))
How can I do that?

Thanks,
Stefi