Named Range maker code

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

This code works okay. I found and modified the original to do a single named range by selecting the cell with the "Name_to-be" in it and run the code.

It produces a Refers To: offset formula to make the range dynamic like this, where the cell selected was H1 and with a name in H1.

=OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H:$H)-1,1)

All that is fine.

Howerer, if the selected cell and name is in cell H5, the code gives up the same offset formula instead of refering to H6 and on down.

I tried to incorporat the sRow value into the formula in place of "R2C" but failed at that. It would also need to change the COUNTA formula part to "Sheet1!$H6:$H???)-1,1)" or whatever.

At a loss to get that done.

Thanks,
Howard


Sub DynamicNameMaker()

Dim Col As Long
Dim sName As String
Dim Sht As String
Dim sRow As Long

'**Select the cell that will be the range name and header location

'grab sheet name
Sht = "'" & ActiveSheet.Name & "'"

With Selection

Col = ActiveCell.Column 'c.Column
sName = ActiveCell.Value
sRow = ActiveCell.Offset(1, 0).Row

If Len(sName) > 1 Then
'replace spaces with underscores
sName = Replace(sName, " ", "_", 1)

MsgBox "The named range name will appear as" _
& vbCr & vbCr & " " & sName _
& vbCr & vbCr & "in the Name Manager."

'create the name
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:= _
"=OFFSET(" & Sht & "!R2C" & Col & ",0,0,COUNTA(" & Sht & "!C" & Col & ")-1,1)"

End If

End With
End Sub
 
If you're looking for a template approach...

My position is that dynamic ranges need to be 'relative' to a fixed
'fully-absolute' named range! So assuming a data table exists from A1
thru G10, and row1 contains headers...

Name: "_Hdr1"
RefersTo: "=$A$1"

Name: "_Hdr1Row"
RefersTo: "=$1:$1"

Name: "_Hdr1Col"
RefersTo: "=$A:$A"

Name: "rngDataList"
RefersTo: "=OFFSET(_Hdr1,0,0,COUNTA(_Hdr1Col),COUNTA(_Hdr1Row))
(Defines the entire xy table of data)

Name: "rngData"
RefersTo: "=OFFSET(_Hdr1,1,0,COUNTA(_Hdr1Col)-1,COUNTA(_Hdr1Row))"
(Defines the data xy only)

Name: "rngHdrs"
RefersTo: "=OFFSET(_Hdr1,0,0,1,COUNTA(_Hdr1Row))"
(Defines the headers xy only)

...and your code can access either of the dynamic ranges as needed. Note
that you can assign each range to its own variant...

Dim vRngData, vRngDataList, vRngHdrs, wksData As Worksheet

Set wksData = ThisWorkbook.Sheets("Data")
With wksData
vRngData = .Range("rngData")
vRngDataList = .Range("rngDataList")
vRngHdrs = .Range("rngHdrs")
End With 'wksData

...so code can directly ref values easier than parsing from the entire
table. You really only need vRngDataList since it holds all values code
would need access to, but some may find having the others more
'convenient' to work with. Personally, I prefer using just 1 var so
updating the worksheet, a listview, and/or a file can be handled more
efficiently. This way the data can be stored in a text file
("data.dat") and loaded into the worksheet and/or a userform for
editing with automated features. Obviously, you could also edit the
text file directly and all will be reflected next time you run your
project.

I normally do this manually when designing a project, but using code
sure would save some time/energy!<g> (I do that with most stuff once I
get it nailed down!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

That looks like the "commercial" version of the "starter version" I need at the moment.

Working with some one who has a truck load of data and needs a dependent drop down system to make selections for construction bids/offers.

The data is a bit askew right now, that is, Headers are vertical in a column and the data is strung across 10 to 70 + columns per header.

And along with the issue that some of the data in the row will need to be another sub category for another drop down.

So, there are a LOT of named ranges that will need to be made, but it can no way be in a single swoop of code. We will have to parse the data.

More of a re-gather some pertinent data, get it in a column decide on the name for the range and put it in the top cell and with that cell selected, run the code, presto we have a properly made dynamic named range. Repeat, repeat etc.

I would like the flexibility of being able to use row 2 or perhaps any row as opposed to only be able to use row 1. And the offset formula that is produced is hard coded to start in row 2 (header/name in row 1) and adjust to data length of the column.

So this is a tool to bypass the burden of mistakes in making dynamic named ranges for the starting Excel user I'm working with.

I tried to adapt this generic formula to the variables in the code but while I got it to compile okay in the vba editor it would error back to the formula when the code ran.

OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A$2:$A$200),1)

Sht, sRow, Col and sName just would not cooperate for me.

Howard
 
That's pretty much exactly how the DV lists are laid out in my
Invoicing: SimpleBookkeeping project! Have a look here at
'IncomeExpense.xlt' to see how the ranges are dynamic for the dependant
DV lists on the Expenses worksheet. You'll need the "NameManager" addin
to view the defined names because most are hidden. (I assume that by
now you already have this addin!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
That's pretty much exactly how the DV lists are laid out in my
Invoicing: SimpleBookkeeping project! Have a look here at
'IncomeExpense.xlt' to see how the ranges are dynamic for the dependant
DV lists on the Expenses worksheet. You'll need the "NameManager" addin
to view the defined names because most are hidden. (I assume that by
now you already have this addin!)
Click to expand...

I don't know what the "NameManager" addin is, so I suppose I don't have it.

I have the Income Expense2 open but I'm not seeing anything I can use as a "One at a time dynamic named range maker".

All I really need is the very same code as my first post that will allow me to use row 1 or any other row as the header row for a single dynamic named range.

Howard
 
The data is a bit askew right now, that is, Headers are vertical in a
column and the data is strung across 10 to 70 + columns per header.

And along with the issue that some of the data in the row will need
to be another sub category for another drop down.

So, there are a LOT of named ranges that will need to be made, but it
can no way be in a single swoop of code. We will have to parse the
data.
Click to expand...

This falls under the 'project design' category, which needs to be
planned out and determined ahead of releasing the 'working copy'!
More of a re-gather some pertinent data, get it in a column decide on
the name for the range and put it in the top cell and with that cell
selected, run the code, presto we have a properly made dynamic named
range. Repeat, repeat etc.
Click to expand...

This is a bandaid solution to compensate for lack of good project
design to start with.
I would like the flexibility of being able to use row 2 or perhaps
any row as opposed to only be able to use row 1. And the offset
formula that is produced is hard coded to start in row 2 (header/name
in row 1) and adjust to data length of the column.
Click to expand...

No reason why you can't start in row 10 if desired. My example only
uses row 1 because that's where data is imported to by default. It can
be any col/row to start with so long as the refs are fully-absolute! said:
So this is a tool to bypass the burden of mistakes in making dynamic
named ranges for the starting Excel user I'm working with.
Click to expand...

And so why I say it's a bandaid!<g> Better, IMO, to get users started
down the right path from the get-go, no?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I don't know what the "NameManager" addin is, so I suppose I don't
have it.

I have the Income Expense2 open but I'm not seeing anything I can use
as a "One at a time dynamic named range maker".
Click to expand...

The name defs are hidden and so is why you need the NameManager addin.
The site is down right now but I can email you my copy...
All I really need is the very same code as my first post that will
allow me to use row 1 or any other row as the header row for a single
dynamic named range.

Howard
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
All I really need is the very same code as my first post that will
allow me to use row 1 or any other row as the header row for a single
dynamic named range.
Click to expand...

Not sure if this will help but I'll give it a shot...

The fpSpread.ocx spreadsheet control I use with VB6 apps doesn't
support DV as Excel does, and so to duplicate dependant dropdowns I
have to use code. The control does support cell types and so I use
CellTypeCombobox for this. (It only allows input of data that fits the
celltype)

The lists can be stored in a hidden sheet or a text file because the
data is managed via an array. When a trigger cell receives data input
(take the Expenses sheet's TransactionDate column for example), the
Expense Category combobox is populated (Cost of Goods,Expenses,Other
Expenses). When the user selects an expense category the ExpenseAccount
combobox gets populated with items you see in the Lists sheet from the
3 categories. When an expense account gets selected the SubAccount
combobox gets populated with items running horizontal from the parent
account.

While it's definitely easier to use DV and dynamic ranges, control is
limited to the number of pre-defined ranges. In this case the lists are
2D where main items are listed in a column and their relative subitems
are listed in their respective row. You could extend a single 2D list
to multiple 2D lists as seen on the Lists sheet for the other dynamic
lists above and below the expenses lists.

The structure of that sheet is rather complex to say the least, but the
important thing is that the named ranges MUST be global in scope to be
used on other sheets. This, of course, is not the case with the way I
handle this project in the VB6 version. Note that the xlt you have only
uses 2 DV cols while the VB6 version uses 3 dropdowns on its expenses
sheet (and 2 instead of 1 on Income).

I guess the point I'm trying to make is that if you're going to go to
all the trouble to add DV lists via code then perhaps an approach
similar to the fpSpread solution would be better handled by populating
as many dependant dropdowns as needed "on-the-fly" via code managed by
sheet events. This will make managing heirarchal subitems much easier
than trying to fanagle DV formulas on-the-fly. What I mean is you can
just insert the delimited list without ref to a range. I expect there's
a limit to the length of a list but prudent use of categories can
remedy that fairly easily. This, of course, requires some careful
planning of the worksheet design.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
When was the site down, is it still down for you?

http://www.jkp-ads.com/officemarketplacenm-en.asp

Regards,
Peter T
Click to expand...

The links in Google search returned a 'server' error. Your link works!
Thanks for posting. The latest version I have is 4.2 but I see there's
4.3 available so I'll have a look at what's new...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top