RC Cell Reference in 2010

[joerg1004]

I have some code which I initially created in Excel 2003.

Sheets("Student Report").Cells(3, 20) = "=IF(RC17<>0,INDEX(Names!C2,MATCH(RC17,Names!C1,0)) & "", "" & INDEX(Names!C3,MATCH(RC17,Names!C1,0)),"""")"

It has worked like a charm. In the spreadsheet cell it showed up as:
=IF(RC17<>0,INDEX(Names!C2,MATCH(RC17,Names!C1,0)) & ", " & INDEX(Names!C3,MATCH(RC17,Names!C1,0)),"")

I am now trying to run my spreadsheet under Excel 2010. I've changed my xls spreadsheet to xlsm and this very code now shows up in the spreadsheet cell as this:
=IF(R[14]C[451]<>0,INDEX(Names!R[-1]C[-17],MATCH(R[14]C[451],Names!R[-2]C[-17],0)) & ", " & INDEX(Names!RC[-17],MATCH(R[14]C[451],Names!R[-2]C[-17],0)),"")

What am I doing wrong?

 

[Andrew Poulsom]

Welcome to MrExcel.

Why have you omitted the FormulaR1C1 property? Excel 2010 has a column RC.

 

[joerg1004]

Thank you so much. I knew there was a simple answer.

I'm familiar with the absolute references and the relative references in []. How do I correctly tell it to look in the current row. Would that be [0]? I use that same kind of formula in several other routines.

What would be the correct syntax for my 2003 line of code?
Sheets("Student Report").Cells(3, 20) = "=IF(RC17<>0,INDEX(Names!C2,MATCH(RC17,Names!C1,0)) & "", "" & INDEX(Names!C3,MATCH(RC17,Names!C1,0)),"""")"

 

[joerg1004]

Let me answer my own question.

Sheets("Student Report").Cells(3, 20).FormulaR1C1 = "=IF(RC17<>0,INDEX(Names!C2,MATCH(RC17,Names!C1,0)) & "", "" & INDEX(Names!C3,MATCH(RC17,Names!C1,0)),"""")

Thanks again for your help. It would have taken me ages to identify this simple syntax issue as the reason.

Thank you so much. I knew there was a simple answer.

I'm familiar with the absolute references and the relative references in []. How do I correctly tell it to look in the current row. Would that be [0]? I use that same kind of formula in several other routines.

What would be the correct syntax for my 2003 line of code?
Sheets("Student Report").Cells(3, 20) = "=IF(RC17<>0,INDEX(Names!C2,MATCH(RC17,Names!C1,0)) & "", "" & INDEX(Names!C3,MATCH(RC17,Names!C1,0)),"""")"

 

[shg]

RC17 is a reference to column Q in the current row.