dynamic range question

I have a formula (written by someone else) that takes a column and makes a
dynamic named range based on how many rows of data there are in a different
column. The code is:

ActiveWorkbook.Names.Add "TaxRegime", _

"=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$1:$IC$999,COUNTA(Central_Benefit!$K$1:$K$999))"

My question is, how can I take this formula and make it dynamic for the
column as well? I want to do a search for the the column header
"tax_regime", then use that instead of saying column IC. (I hope that makes
sense).

Any help is very much appreciated. :-)

Dim iLastRow As Long
Dim iLastCol As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
iLastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
ActiveWorkbook.Names.Add "TaxRegime", _
"=Central_Benefit!$IC$1:$" & _
Split(Columns(iLastCol).Address(, False), ":")(1) & _
"$" & iLastRow


--
HTH

Bob Phillips

"Robin" <Robin@discussions.microsoft.com> wrote in message
news:5029061B-AFB7-482C-A8EE-DAF0BA8207C9@microsoft.com...
> I have a formula (written by someone else) that takes a column and makes a
> dynamic named range based on how many rows of data there are in a
different
> column. The code is:
>
> ActiveWorkbook.Names.Add "TaxRegime", _
>
>
"=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$1:$IC$999,COUNTA(Central_B
enefit!$K$1:$K$999))"
>
> My question is, how can I take this formula and make it dynamic for the
> column as well? I want to do a search for the the column header
> "tax_regime", then use that instead of saying column IC. (I hope that
makes
> sense).
>
> Any help is very much appreciated. :-)
>
>

So far, so good, but we still have one instance of IC in there and I'm not
familiar enough with the split function to know how to put that in there
instead (just after central_benefit in the names.add). Can you help? Thanks
again and again!

"Bob Phillips" wrote:

> Dim iLastRow As Long
> Dim iLastCol As Long
> iLastRow = Cells.Find(What:="*", _
> After:=Range("A1"), _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious).Row
> iLastCol = Cells.Find(What:="*", _
> After:=Range("A1"), _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious).Column
> ActiveWorkbook.Names.Add "TaxRegime", _
> "=Central_Benefit!$IC$1:$" & _
> Split(Columns(iLastCol).Address(, False), ":")(1) & _
> "$" & iLastRow
>
>
> --
> HTH
>
> Bob Phillips
>
> "Robin" <Robin@discussions.microsoft.com> wrote in message
> news:5029061B-AFB7-482C-A8EE-DAF0BA8207C9@microsoft.com...
> > I have a formula (written by someone else) that takes a column and makes a
> > dynamic named range based on how many rows of data there are in a
> different
> > column. The code is:
> >
> > ActiveWorkbook.Names.Add "TaxRegime", _
> >
> >
> "=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$1:$IC$999,COUNTA(Central_B
> enefit!$K$1:$K$999))"
> >
> > My question is, how can I take this formula and make it dynamic for the
> > column as well? I want to do a search for the the column header
> > "tax_regime", then use that instead of saying column IC. (I hope that
> makes
> > sense).
> >
> > Any help is very much appreciated. :-)
> >
> >
>
>
>

I got it. Thanks again for your help. :-)

Dim iLastRow As Long
Dim iCol As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
iCol = Cells.Find(What:="tax_regime", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
ActiveWorkbook.Names.Add "regime", _
"=Central_Benefit!$" & Split(Columns(iCol).Address(, False), ":")(1)
& "$1:$" & _
Split(Columns(iCol).Address(, False), ":")(1) & _
"$" & iLastRow

"Robin" wrote:

> So far, so good, but we still have one instance of IC in there and I'm not
> familiar enough with the split function to know how to put that in there
> instead (just after central_benefit in the names.add). Can you help? Thanks
> again and again!
>
> "Bob Phillips" wrote:
>
> > Dim iLastRow As Long
> > Dim iLastCol As Long
> > iLastRow = Cells.Find(What:="*", _
> > After:=Range("A1"), _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlPrevious).Row
> > iLastCol = Cells.Find(What:="*", _
> > After:=Range("A1"), _
> > SearchOrder:=xlByColumns, _
> > SearchDirection:=xlPrevious).Column
> > ActiveWorkbook.Names.Add "TaxRegime", _
> > "=Central_Benefit!$IC$1:$" & _
> > Split(Columns(iLastCol).Address(, False), ":")(1) & _
> > "$" & iLastRow
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Robin" <Robin@discussions.microsoft.com> wrote in message
> > news:5029061B-AFB7-482C-A8EE-DAF0BA8207C9@microsoft.com...
> > > I have a formula (written by someone else) that takes a column and makes a
> > > dynamic named range based on how many rows of data there are in a
> > different
> > > column. The code is:
> > >
> > > ActiveWorkbook.Names.Add "TaxRegime", _
> > >
> > >
> > "=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$1:$IC$999,COUNTA(Central_B
> > enefit!$K$1:$K$999))"
> > >
> > > My question is, how can I take this formula and make it dynamic for the
> > > column as well? I want to do a search for the the column header
> > > "tax_regime", then use that instead of saying column IC. (I hope that
> > makes
> > > sense).
> > >
> > > Any help is very much appreciated. :-)
> > >
> > >
> >
> >
> >

LOL. I thought you wanted to start at IC regardless. Caused some oddities in
my test, because I ended before IC.

--
HTH

Bob Phillips

"Robin" <Robin@discussions.microsoft.com> wrote in message
news:7C035696-EE3F-4131-88AE-05DB5E749A58@microsoft.com...
> I got it. Thanks again for your help. :-)
>
> Dim iLastRow As Long
> Dim iCol As Long
> iLastRow = Cells.Find(What:="*", _
> After:=Range("A1"), _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious).Row
> iCol = Cells.Find(What:="tax_regime", _
> After:=Range("A1"), _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious).Column
> ActiveWorkbook.Names.Add "regime", _
> "=Central_Benefit!$" & Split(Columns(iCol).Address(, False),
":")(1)
> & "$1:$" & _
> Split(Columns(iCol).Address(, False), ":")(1) & _
> "$" & iLastRow
>
> "Robin" wrote:
>
> > So far, so good, but we still have one instance of IC in there and I'm
not
> > familiar enough with the split function to know how to put that in there
> > instead (just after central_benefit in the names.add). Can you help?
Thanks
> > again and again!
> >
> > "Bob Phillips" wrote:
> >
> > > Dim iLastRow As Long
> > > Dim iLastCol As Long
> > > iLastRow = Cells.Find(What:="*", _
> > > After:=Range("A1"), _
> > > SearchOrder:=xlByRows, _
> > > SearchDirection:=xlPrevious).Row
> > > iLastCol = Cells.Find(What:="*", _
> > > After:=Range("A1"), _
> > > SearchOrder:=xlByColumns, _
> > > SearchDirection:=xlPrevious).Column
> > > ActiveWorkbook.Names.Add "TaxRegime", _
> > > "=Central_Benefit!$IC$1:$" & _
> > > Split(Columns(iLastCol).Address(, False), ":")(1) & _
> > > "$" & iLastRow
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Robin" <Robin@discussions.microsoft.com> wrote in message
> > > news:5029061B-AFB7-482C-A8EE-DAF0BA8207C9@microsoft.com...
> > > > I have a formula (written by someone else) that takes a column and
makes a
> > > > dynamic named range based on how many rows of data there are in a
> > > different
> > > > column. The code is:
> > > >
> > > > ActiveWorkbook.Names.Add "TaxRegime", _
> > > >
> > > >
> > >
"=Central_Benefit!$IC$1:INDEX(Central_Benefit!$IC$1:$IC$999,COUNTA(Central_B
> > > enefit!$K$1:$K$999))"
> > > >
> > > > My question is, how can I take this formula and make it dynamic for
the
> > > > column as well? I want to do a search for the the column header
> > > > "tax_regime", then use that instead of saying column IC. (I hope
that
> > > makes
> > > > sense).
> > > >
> > > > Any help is very much appreciated. :-)
> > > >
> > > >
> > >
> > >
> > >