Would like help making a function

[Sarah H.]

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

 

[Sarah H.]

=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 H.]

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

 

[Bernard Liengme]

So now this is a self-help newsgroup <grin>
But thanks for the function
Bernard

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

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

 

[Sarah H.]

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
--

So now this is a self-help newsgroup <grin>
But thanks for the function
Bernard

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

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