How to Insert Variables into Formula's

  • Thread starter Thread starter Ardy
  • Start date Start date
A

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
 
hi Ardy,

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

or

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


--
isabelle



Le 2011-12-12 17:22, Ardy a écrit :
 
?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
 
...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
 
Back
Top