syntax when using a variable in a formula

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" <adorr@idirect.ca> wrote in message
news:%235UrFtkcGHA.3344@TK2MSFTNGP03.phx.gbl...
> 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