replacing varaibles in formula

[monika]

I have "G:\04 Assembly Cost\+06 FY
03_04\TCR2003_Assembly_0305.xls" in a
variable path1 and the worksheet name "TCR2003 by packages"
in another
variable "wkshName".

how can I replace the 'G:\04 Assembly Cost\+06 FY
03_04\[TCR2003_Assembly_0305.xls]TCR2003 by packages' in
the vlookup formula
below by the variables path1 and wkshName???

Cells(rowSt, z) = "=( VLOOKUP(" & Cells(rowSt,
packRng.Column).Address &
",'G:\04 Assembly Cost\+06 FY
03_04\[TCR2003_Assembly_0305.xls]TCR2003 by
packages'!$B$4:$G$251,4,FALSE)" & ")"

Thanks
monika

 

[BrianB]

It is better to break it down as much as possible, so something lik
this (UNTESTED - but you should get the idea) :-

'---------------------------------------------
Sub test()
Dim MyPath As String
Dim MyBook As String
Dim MySheet As String
'--------------------------------
MyPath = "G:\04 Assembly Cost\+06 FY 03_04\"
MyBook = "TCR2003_Assembly_0305.xls"
MySheet = "TCR2003"
'------------------------------------------
ActiveSheet.Cells(rowSt, z).Value = _
"=(VLOOKUP(" & Cells(rowSt, packrng.Column).Address _
& ",'" & MyPath _
& "[" & MyBook & "]" _
& MySheet & "'" _
& "!$B$4:$G$251,4,FALSE))"
End Sub
'-------------------------------------------------