I would let excel do the work for me:

dim ColE as range
dim ColA as range
dim TaskWSName as String

'watch out for "E8" <--with double quotes
TaskWSName = "T" & ActiveSheet.Range("E8").value & "_Master"

with worksheets(taskwsname)
set cole=.range("e:E")
set colA = .range("a:a")
end with

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & cole.address(external:=true) & ",match(a8," & _
cola.address(external:=true) & ",0))"


======
You could build the strings yourself,

Worksheets("PrintReport").Range("A1").Formula _
= "=index('" & TaskWSName & "'!a:a,match(a8," & _
"'" & TaskWSName & "'!a:a,0))"

===
You could drop the $ signs by using:

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & ColE.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",match(a8," & _
ColA.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",0))"

anny wrote:

greetings

I have code that generates names (eg T2_Master)
Dim TaskWSName as String
TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"

Later, I want to have code that refers to Worksheets having these names.
Here's an eg:
Worksheets("PrintReport").Range("A1").Formula =
"=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0 ))

How do I refer to T2_Master! in the INDEX or the MATCH statements? Using
TaskWSName! doesn't work.

Thanks in advance
anny

--

Dave Peterson