Using R1C1 formula referencing other WB and SH

[LaBamba]

Hi guys,

I am using the following code to attach a formula as a string to a cell.


strFormula = "='[" & wbCustomerFile.Name & "]" & shPS.Name & "'!AL2"

It is returning this value in the cell.

='[Total 1.xlsx]NR'!'AL2'
Which is the correct notation...sort of. I need it to get rid of the last ' and the ' after the !.

What am i missing here?

Let me know.

Thanks,

[Kenneth Hobs]

Try using the Immediate window to view results of debug.print. I don't see those characters being added.

Sub ken()
    Dim strFormula As String
  strFormula = "='[" & ThisWorkbook.Name & "]" & ActiveSheet.Name & "'!AL2"
  Debug.Print strFormula
End Sub