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. :-)

dynamic range question
 

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" wrote in message
...
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. :-)

dynamic range question
 

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" wrote in message
...
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. :-)

dynamic range question
 

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" wrote in message
...
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. :-)

dynamic range question
 

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" wrote in message
...
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" wrote in message
...
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. :-)