excel function help

[oaishm]

I have two ranges named PReq and QOutput.

I have this procedure:

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(=QOutput!$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

 

[Sheeloo]

If you just want to build a formula then simply pass PReq and Qouput as
strings... why use them as Range in the function call?

Also to get the address of the defined names use
PReq.RefersToRange.Address
instead of
PReq.Name

Try (after verifying the quotes placement...

Sub test_dependency()
Call dependency(Worksheets("Sheet1"), "PReq", "QOutput", "testtitle")
End Sub

Sub dependency(Result As Worksheet, Ratiorange As String, Qrange As String,
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," & z - [Yearstart].Value + 1 & _
",false) * index(Ratiorange," & rowitem & "," & colitem & ")"
End Sub

 

[oaishm]

If you just want to build a formula then simply pass PReq and Qouput as
strings... why use them as Range in the function call?

Also to get the address of the defined names use
PReq.RefersToRange.Address
instead of
PReq.Name

Try (after verifying the quotes placement...

Sub test_dependency()
Call dependency(Worksheets("Sheet1"), "PReq", "QOutput", "testtitle")
End Sub

Sub dependency(Result As Worksheet, Ratiorange As String, Qrange As String,
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," & z - [Yearstart].Value + 1 & _
",false) * index(Ratiorange," & rowitem & "," & colitem & ")"
End Sub
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.

I have two ranges named PReq and QOutput.

I have this procedure:

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:

I'll take

but I get

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- Hide quoted text -

- Show quoted text -

Really? Is that the best that Microsoft can do? I was going to use
the ranges as both a range and a function. There's no property to
putt the name of the range? That's too bad. Thanks for your help. I
must be doing something uncommon if I want ot use a range in a
procedure to refer to cell values and I want to use it to build up a
formula both in the same procedure

 

[Sheeloo]

Try
Sub listNames()
Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
For r = 1 To nms.Count
wks.Cells(r, 2).Value = nms(r).Name
wks.Cells(r, 3).Value = nms(r).RefersToRange.Address
Next
End Sub

Just noticed that you are passing PReq as range and not as Name
Range does not have a name property... as it has no name

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



:

 

[Charles Williams]

see response in Programming

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

If you just want to build a formula then simply pass PReq and Qouput as
strings... why use them as Range in the function call?

Also to get the address of the defined names use
PReq.RefersToRange.Address
instead of
PReq.Name

Try (after verifying the quotes placement...

Sub test_dependency()
Call dependency(Worksheets("Sheet1"), "PReq", "QOutput", "testtitle")
End Sub

Sub dependency(Result As Worksheet, Ratiorange As String, Qrange As
String,
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," & z - [Yearstart].Value + 1 & _
",false) * index(Ratiorange," & rowitem & "," & colitem & ")"
End Sub
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.

I have two ranges named PReq and QOutput.

I have this procedure:

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:

I'll take

but I get

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- Hide quoted text -

- Show quoted text -

Really? Is that the best that Microsoft can do? I was going to use
the ranges as both a range and a function. There's no property to
putt the name of the range? That's too bad. Thanks for your help. I
must be doing something uncommon if I want ot use a range in a
procedure to refer to cell values and I want to use it to build up a
formula both in the same procedure