Use Variable in FormulaR1C1

[bianliaw]

I have program like this

[code/]
Public Function aUnit(Element As Integer)
aUnit = Array("AA", "AB", "AC")(Element)
End Function

Private Sub Refresh_Click()
For nLoop = 0 To nMaxUnit
cUnit = aUnit(nLoop)

For nCol = 21 To 25
For nRow = 8 To nMaxRow
Sheets(cUnit).Cells(nRow, nCol).FormulaR1C1 = _
"='[MON2005.xls]cUnit'!Cells(nRow, nCol)"
Next
Next
Next

End Sub
[/code]


There is an error at
"='[MON2005.xls]cUnit'!Cells(nRow, nCol)"

how to correct this error ?

Thank In Advice.

 

[Zathras]

It's not entirely clear what you are trying to do, but if I have guessed correctly, this may be what you are looking for:

 Sheets(cUnit).Cells(nRow, nCol).FormulaR1C1 = _
   "=[MON2005.xls]" & cUnit & "!R" & nRow & "C" & nCol

If not, provide a few more details as to what it is you want to accomplish.

 

[xlbo]

Why bother with FormulaR1C1 ?? just use .Formula. Then you can use:

"='[MON2005.xls]cUnit'!" & Cells(nRow, nCol).address


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question