syntax when using a variable in a formula

[anny]

hello XLers

I think I'm missing something obvious here, but I could use some help.

I have code that, among other things, pastes formulas into cells. Part of
one of the formulas is:

FormulaStr9 = "=IF(OR .... more code here ... & INDEX(Sheet23!E:E,
MATCH(A8, Sheet23!A: A .....etc

All works well, but on other sheets I keep having to Replace Sheet23 with
Sheet24, Sheet 25, etc.

Now I have a variable called SheetName, but I can't get the syntax of the
formula to work when using it. How do I use SheetName in the formula? When
SheetName = Sheet23, the formula should give the same result as the one
shown above.

Much thanks for any help
anny

 

[Gary Keramidas]

maybe you can adapt something like this

Sub test()
Dim i As Long
Dim sStr As String
For i = 1 To 3
sStr = ThisWorkbook.VBProject _
.VBComponents("Sheet" & i) _
.Properties("Name").Value
MsgBox "Tab name of Sheet" & i & _
" is " & sStr
Next

End Sub

 

[Bri]

Lets say the formula is to be pasted into cell A1. Try entering the formula
the way you have it, then use something like:

ActiveSheet.Range("A1").Replace "Sheet23", SheetName

cheers
Bri