How to Insert Variables into Formula's

[Ardy]

I have the Following Code. I understand it is not optimized, the
question is how do i place the captured tab name in the formula. what
I am trying is not working.

Public Sub Tester001()

Dim strClickedSht As String
Dim TabName As String

Sheets("Unit 2 Week 2 Test Standards An").Select
strClickedSht = ActiveSheet.Name
TabName = strClickedSht
MsgBox TabName 'for testing
Sheets("Standard Analysis").Select
Range("A6").Select
ActiveCell.FormulaR1C1 = "=('TabName'!R[2]C)"
End Sub

 

[isabelle]

hi Ardy,

ActiveCell.FormulaR1C1 = "=('" & TabName & "'!R[2]C)"

or

ActiveCell.Formula = "=('" & TabName & "'!A8)"


--
isabelle



Le 2011-12-12 17:22, Ardy a écrit :

 

[Ardy]

Thank You.

 

[GS]

?Optimized...

Public Sub Tester001()
Const TabName As String = "Unit 2 Week 2 Test Standards An"
Sheets("Standard Analysis").Range("A6").FormulaR1C1 = _
"=('" & TabName & "'!R[2]C)"
End Sub

 

[GS]

...or this one-liner:

Public Sub Tester001()
Sheets("Standard Analysis").Range("A6").FormulaR1C1 = _
"=('Unit 2 Week 2 Test Standards An'!R[2]C)"
End Sub