Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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. "Charles Williams" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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 "oaishm" wrote in message ... 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. "Charles Williams" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
named ranges in building excel formulas from a string
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 "Charles Williams" wrote: 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 "oaishm" wrote in message ... 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. "Charles Williams" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges and pasting formulas with named references | Excel Programming | |||
building formulas that change frequently using named cell ranges | Excel Worksheet Functions | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
Delete named ranges beginning with a string | Excel Programming | |||
protecting formulas with named ranges | Excel Programming |