named ranges in building excel formulas from a string

[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

 

[Mark]

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

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.

 

[Charles Williams]

The default property of a Name is its Refersto not its Name so you need to
use the not-very-obvious syntax of
QRange.Name.Name

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

 

[oaishm]

Charles

I've read
VBA and Macros for Microsoft Excel by Mr Excel, Bill Jelen
The Excel bible series by John Wallenbach
Excel 2007 bible from wrox

I've also asked
The google message board for excel
Mr. Excel's message board
excelforum

NO ONE else had this answer. How could you have known this apparent arcana
of syntax when no one else in the world know it.

In any event. Thank you so much. I've been on this problem for four days.
The learning curve is unbelievably steep and I still don't understand why you
would need name.name. How would you even figure it out.

The default property of a Name is its Refersto not its Name so you need to
use the not-very-obvious syntax of
QRange.Name.Name

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

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

 

[Charles Williams]

I agree that its not obvious, but if you go through it step by step -

Range defines a range object: its default property is Value (should be
Value2 but thats another story)

Range.Name defines a Name Object (if it exists, the Name assigned to that
Range). The Default property of a Name object is its refersto (which is what
you want most of the time).

So if you don't want the default you have to be specific: if what you want
is the Name property of the Name object then you need to use Name.Name

But yeah its not obvious.

I'm sure that John Walkenbach mentions this somewhere in one or more of his
books.


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

Charles

I've read
VBA and Macros for Microsoft Excel by Mr Excel, Bill Jelen
The Excel bible series by John Wallenbach
Excel 2007 bible from wrox

I've also asked
The google message board for excel
Mr. Excel's message board
excelforum

NO ONE else had this answer. How could you have known this apparent
arcana
of syntax when no one else in the world know it.

In any event. Thank you so much. I've been on this problem for four
days.
The learning curve is unbelievably steep and I still don't understand why
you
would need name.name. How would you even figure it out.

The default property of a Name is its Refersto not its Name so you need
to
use the not-very-obvious syntax of
QRange.Name.Name

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

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

 

[oaishm]

These are not well covered concepts you are discussing with "default value"
Which object get default values? Like what's the default value of
worksheets("asheet")? the sheet object?

I looked again in the wallenbach book to see if they cover the concept of
default value. No such luck. These books don't like concepts. They like
how to's and if what you want to do is not in the book, there aren't any
concepts to get you there. The worst is there's no universal set of values
that objects have and common labels that refer to them.

I once had a procedure with a range named Years. I made a statement like:
Years = [Years]

It failed miserably. After two days and many forums I figured out I can
either do Years2 = [Years] or Years = range("Years"). Again, no reason why
this is so

I agree that its not obvious, but if you go through it step by step -

Range defines a range object: its default property is Value (should be
Value2 but thats another story)

Range.Name defines a Name Object (if it exists, the Name assigned to that
Range). The Default property of a Name object is its refersto (which is what
you want most of the time).

So if you don't want the default you have to be specific: if what you want
is the Name property of the Name object then you need to use Name.Name

But yeah its not obvious.

I'm sure that John Walkenbach mentions this somewhere in one or more of his
books.


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

Charles

I've read
VBA and Macros for Microsoft Excel by Mr Excel, Bill Jelen
The Excel bible series by John Wallenbach
Excel 2007 bible from wrox

I've also asked
The google message board for excel
Mr. Excel's message board
excelforum

NO ONE else had this answer. How could you have known this apparent
arcana
of syntax when no one else in the world know it.

In any event. Thank you so much. I've been on this problem for four
days.
The learning curve is unbelievably steep and I still don't understand why
you
would need name.name. How would you even figure it out.

The default property of a Name is its Refersto not its Name so you need
to
use the not-very-obvious syntax of
QRange.Name.Name

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

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