On Apr 14, 10:00*pm, oaishm wrote:
I have two ranges named PReq and QOutput.

I have this procedu

Sub test_dependency()
Call dependency(Worksheets("PP"), [PReq], [QOutput], "testtitle")
End Sub

And I have:

Sub dependency(Result As Worksheet, Ratiorange As Range, Qrange As
Range, Title As String)
row_id = some array which results in a value
[Yearstart].value = 5
rowitem = 5
colitem = 5
z=10
Result.Cells(rowwriter, z).formula = "=vlookup(" & row_id(rowitem, 1)
& "," & Qrange.name & "," & z - [Yearstart].Value + 1 & ",false) *
index(" & Ratiorange.name & "," & rowitem & "," & colitem & ")"

I desperately want the formula to look like:

=vlookup(4,PReq,6,false)*index(QOutput,5,5)

I'll take

=vlookup(4,PReq!$b$5:$h$100,6,false)*index(QOutput !$b$5:$g$10,5,5)

but I get

=vlookup(4,=PReq!$b$5:$h$100,6,false)*index(=QOutp ut!$b$5:$g$10,5,5)

I've tried Qrange.name, .address, .value, .text anything. they all
dont' work. Then I tried to go through the Locals window and when I
clicked onto Qrange - Cells - Name, lo and behold, it was "QOutput".
So then I tried

?Qrange.cells.name in the immediate window and got a syntax error

same with:

?[Qrange].cells.name and ?Range("QRange").cells.name

Tell me your learning curve is as steep as mine because this should be
very obvious

Here is a link that may help.http://groups.google.com/group/
microsoft.public.excel.programming/browse_thread/thread/
babb14d1b83a3521/24e4c483810e4a75?hl=en&lnk=gst&q=referencing+named
+ranges+in+vba#24e4c483810e4a75

You may have to do some string manipulation do get rid of the extra
"=" that are coming from your 2 .name references.