Using Variable Index with FormulaR1C1

[ranuse]

Hi,

I'm having difficulty finding cells in a specific column of another worksheet and dividing the cells by the values in the total column and storing the result in the current worksheet.


'P will represent the specified column index in other worksheet

Dim P As Long

'PR will represent the specified column index in current worksheet

Dim PR As Long
Dim Total As Long, Rows As Long

Sheets("Sheet1").Activate

P = Rows(1).Find("P").Column
F= Rows(1).Find("F").Column
Total= Rows(1).Find("Total").Column
Rows = ActiveSheet.UsedRange.Rows.Count

Sheets("Sheet2").Activate

Range(Cells(2, PR), Cells(Rows, PR)).FormulaR1C1 = _
"=('Sheet1'!RC[Passed] /'Sheet1'!RC[Total])"


I get an "Application-defined or object-defined error" from the RC[Passed] code segment. Is there a fix or alternative methods to perform this functionality?

Thanks.

[Bob Phillips]

'P will represent the specified column index in other worksheet
Dim P As Long
'PR will represent the specified column index in current worksheet
Dim PR As Long
Dim Total As Long, Rows As Long

Sheets("Sheet1").Activate

P = Rows(1).Find("P").Column
F = Rows(1).Find("F").Column
Total = Rows(1).Find("Total").Column
Rows = ActiveSheet.UsedRange.Rows.Count

Sheets("Sheet2").Activate

Range(Cells(2, PR), Cells(Rows, PR)).FormulaR1C1 = _
"=('Sheet1'!RC[" & Passed & "] /'Sheet1'!RC[" & Total & "])"

[mikerickson]

Should the square brackets be removed so the column reference is absolute?