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