If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Would like help making a function
Hi, folks!
I'd like help coding a VBA function. Here's what I'm looking for: I want to be able to use the function in a formula like this: dataCol("Foo") where "Foo" is a match from a predefined header column. I'm in Excel 2007. The idea is to be able to move or add columns to my design dynamically and have the named ranges keep up. I'm already doing this with good results. I have set up various functions, formulas, and ranges in the Name Manager (thanks so much to its authors!) such that: o "HeadRow" finds the row number my header is on (and I can change it!) o "rgHeadRow" is the range of "HeadRow:HeadRow" (via the INDIRECT function) so I'll be able to use MATCH there. o "DatColA" is my range of data in Column A And so on. o "DatColFoo" is set to "=OFFSET(DatColA,0,MATCH("Foo",rgHeadRow,0)-1)" So I can use "DatColFoo" in functions such as SUMPRODUCT or whatever, and it will be the data column whose header says "Foo". It's very handy. If I later insert a column, "DatColFoo" still ends up correct. But I don't want to do this with 20-30 columns, naming each one manually. That's the point. So I want to have the function as described up-top. Then I'd be able to use in formulas things like this: =SUBTOTAL(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) I'm doing that already. But I have had to predefine "DatColFoo" and "DatColBar" and all the others, and that's what I want to let my function do for me. I've ever only made one or two functions before, and I don't really know how to approach this. Will someone take up the challenge? Much obliged, Sarah |
#2
|
|||
|
|||
Would like help making a function
"Sarah H." wrote in message ... =SUBTOTAL(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) Whoops, that was supposed to say: =SUMPRODUCT(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) But I'm sure you got the idea. -- Sarah Hi, folks! I'd like help coding a VBA function. Here's what I'm looking for: I want to be able to use the function in a formula like this: dataCol("Foo") . . . |
#3
|
|||
|
|||
Would like help making a function
I grew impatient so cobbled something together. It took much trial and
error. It works! I'm happy to have figured it out. I'd like any constructive criticism, in any case. This is for XL 2007. Here it is: Function DataCol(myHeader) Dim HdrR, EndR As Long Dim myCol As Long EndR = Range("A65536").End(xlUp).Row HdrR = Application.WorksheetFunction.Match("*", Range("a1:a" & EndR), 0) myCol = Application.WorksheetFunction.Match(myHeader, Range(HdrR & ":" & HdrR), 0) Set DataCol = Range(Cells(HdrR + 1, myCol), Cells(EndR, myCol)) End Function -- Sarah "Sarah H." wrote in message ... Hi, folks! I'd like help coding a VBA function. Here's what I'm looking for: I want to be able to use the function in a formula like this: dataCol("Foo") where "Foo" is a match from a predefined header column. I'm in Excel 2007. The idea is to be able to move or add columns to my design dynamically and have the named ranges keep up. I'm already doing this with good results. I have set up various functions, formulas, and ranges in the Name Manager (thanks so much to its authors!) such that: o "HeadRow" finds the row number my header is on (and I can change it!) o "rgHeadRow" is the range of "HeadRow:HeadRow" (via the INDIRECT function) so I'll be able to use MATCH there. o "DatColA" is my range of data in Column A And so on. o "DatColFoo" is set to "=OFFSET(DatColA,0,MATCH("Foo",rgHeadRow,0)-1)" So I can use "DatColFoo" in functions such as SUMPRODUCT or whatever, and it will be the data column whose header says "Foo". It's very handy. If I later insert a column, "DatColFoo" still ends up correct. But I don't want to do this with 20-30 columns, naming each one manually. That's the point. So I want to have the function as described up-top. Then I'd be able to use in formulas things like this: [next line corrected] =SUMPRODUCT(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) I'm doing that already. But I have had to predefine "DatColFoo" and "DatColBar" and all the others, and that's what I want to let my function do for me. I've ever only made one or two functions before, and I don't really know how to approach this. Will someone take up the challenge? Much obliged, Sarah |
#4
|
|||
|
|||
Would like help making a function
So now this is a self-help newsgroup grin
But thanks for the function Bernard "Sarah H." wrote in message ... I grew impatient so cobbled something together. It took much trial and error. It works! I'm happy to have figured it out. I'd like any constructive criticism, in any case. This is for XL 2007. Here it is: Function DataCol(myHeader) Dim HdrR, EndR As Long Dim myCol As Long EndR = Range("A65536").End(xlUp).Row HdrR = Application.WorksheetFunction.Match("*", Range("a1:a" & EndR), 0) myCol = Application.WorksheetFunction.Match(myHeader, Range(HdrR & ":" & HdrR), 0) Set DataCol = Range(Cells(HdrR + 1, myCol), Cells(EndR, myCol)) End Function -- Sarah "Sarah H." wrote in message ... Hi, folks! I'd like help coding a VBA function. Here's what I'm looking for: I want to be able to use the function in a formula like this: dataCol("Foo") where "Foo" is a match from a predefined header column. I'm in Excel 2007. The idea is to be able to move or add columns to my design dynamically and have the named ranges keep up. I'm already doing this with good results. I have set up various functions, formulas, and ranges in the Name Manager (thanks so much to its authors!) such that: o "HeadRow" finds the row number my header is on (and I can change it!) o "rgHeadRow" is the range of "HeadRow:HeadRow" (via the INDIRECT function) so I'll be able to use MATCH there. o "DatColA" is my range of data in Column A And so on. o "DatColFoo" is set to "=OFFSET(DatColA,0,MATCH("Foo",rgHeadRow,0)-1)" So I can use "DatColFoo" in functions such as SUMPRODUCT or whatever, and it will be the data column whose header says "Foo". It's very handy. If I later insert a column, "DatColFoo" still ends up correct. But I don't want to do this with 20-30 columns, naming each one manually. That's the point. So I want to have the function as described up-top. Then I'd be able to use in formulas things like this: [next line corrected] =SUMPRODUCT(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) I'm doing that already. But I have had to predefine "DatColFoo" and "DatColBar" and all the others, and that's what I want to let my function do for me. I've ever only made one or two functions before, and I don't really know how to approach this. Will someone take up the challenge? Much obliged, Sarah |
#5
|
|||
|
|||
Would like help making a function
You're welcome, Bernard! :-)
The oddest thing is, this woks on the sheet I wrote it for, but on a similar sheet it doesn't work right. The other sheet has filtered data. If that's what's making it not work, I still need some help. My (previously devised) named ranges worked fine with the filtered data on that other sheet. Anybody? (And how do I add in some sane error-handling in my function?) Much obliged, Sarah -- "Bernard Liengme" wrote in message ... So now this is a self-help newsgroup grin But thanks for the function Bernard "Sarah H." wrote in message ... I grew impatient so cobbled something together. It took much trial and error. It works! I'm happy to have figured it out. I'd like any constructive criticism, in any case. This is for XL 2007. Here it is: Function DataCol(myHeader) Dim HdrR, EndR As Long Dim myCol As Long EndR = Range("A65536").End(xlUp).Row HdrR = Application.WorksheetFunction.Match("*", Range("a1:a" & EndR), 0) myCol = Application.WorksheetFunction.Match(myHeader, Range(HdrR & ":" & HdrR), 0) Set DataCol = Range(Cells(HdrR + 1, myCol), Cells(EndR, myCol)) End Function -- Sarah "Sarah H." wrote in message ... Hi, folks! I'd like help coding a VBA function. Here's what I'm looking for: I want to be able to use the function in a formula like this: dataCol("Foo") where "Foo" is a match from a predefined header column. I'm in Excel 2007. The idea is to be able to move or add columns to my design dynamically and have the named ranges keep up. I'm already doing this with good results. I have set up various functions, formulas, and ranges in the Name Manager (thanks so much to its authors!) such that: o "HeadRow" finds the row number my header is on (and I can change it!) o "rgHeadRow" is the range of "HeadRow:HeadRow" (via the INDIRECT function) so I'll be able to use MATCH there. o "DatColA" is my range of data in Column A And so on. o "DatColFoo" is set to "=OFFSET(DatColA,0,MATCH("Foo",rgHeadRow,0)-1)" So I can use "DatColFoo" in functions such as SUMPRODUCT or whatever, and it will be the data column whose header says "Foo". It's very handy. If I later insert a column, "DatColFoo" still ends up correct. But I don't want to do this with 20-30 columns, naming each one manually. That's the point. So I want to have the function as described up-top. Then I'd be able to use in formulas things like this: [next line corrected] =SUMPRODUCT(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C")) I'm doing that already. But I have had to predefine "DatColFoo" and "DatColBar" and all the others, and that's what I want to let my function do for me. I've ever only made one or two functions before, and I don't really know how to approach this. Will someone take up the challenge? Much obliged, Sarah |
Thread Tools | |
Display Modes | |
|
|