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

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

--


Gary


"anny" wrote in message
...
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

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