Hi Howard,
No worries :) Ask as many questions as needed. Best to send your replies
to the list though, so you get the benefit of other replies (if only because
I may not have time to reply myself). This also allows other VBA learners
can benefit from the conversation.
A defined name (either via range.name="mycolumn" or names.add) that refers
to a range sticks to the same data/cells even when rows and cells are
inserted and deleted because Excel updates those name references
automatically whenever those events occur. This is just as Excel does the
same for references in cell formulae when insert and deletes occur.
Usually if you are referring to ranges in VBA you do not use defined names,
but just use range variables. Use defined names when the names need to be
used from cell formulae, or as one method of retaining those names between
sessions (file close, reopen).
If you use only a range variable, and column C contains a certain field of
data -- say, Birthday -- then you or the user inserts a column before C to
add another field -- say, Anniversary... And you use code similar to the
following:
Public Birthday As Range
Sub Workbook_Open()
Set Birthday = Range("C:C")
'Column gets inserted here with heading "Anniversary"
MsgBox Birthday.Cells(1,1).Value ' Display the first cell value (column
heading)
End Sub
...open the file, allowing the above event to execute. Message should say
"Birthday".
...then close the file...
...reopen...message will say "Anniversary".
The problem with the above is that although the Range variable will adapt
correctly when a column is inserted and still refer to the Birthday column,
every time the file is opened it is set explicitly to refer to column C
again.
A defined name is a simple solution since it is saved between sessions and
automatically adapts.
The next simplest solution that comes to mind, and is actually even smarter
(adapts if user just changes the text of the column heading without
inserting or deleting columns) is to use range.find or
worksheetfunction.match to find the column with the correct heading, and use
that. For example:
Set Birthday = Rows(1).Find("Birthday").EntireColumn
...or:
Set Birthday = Sheets("MySheet").UsedRange.Rows(1).Find("Birthday",
LookIn:=xlValues).EntireColumn
...or:
Set Birthday = Columns(WorksheetFunction.Match("Birthday", Range("1:1"), 0))
...or:
With Sheets("MySheet").UsedRange
Set Birthday = .Columns(WorksheetFunction.Match("Birthday", .Rows(1),
0)) ' Resulting range will be the used part of the Birthday column, e.g.
C1:C101
End With
The above methods and their variants will always find the column with the
given heading, so they are very adaptable and will find the right column
after closing/reopening the file or inserting/deleting columns, or changing
column headings.
Asa
-----Original Message-----
From: Domain Admin [mailto:domainqu...@gmail.com]
Sent: Monday, March 19, 2012 3:23 PM
To: Asa Rossoff
Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
You are a very helpful fellow. You were right the first time. I just
did not understand that from your answer (I am still reading Excel VBA
Programming for Dummies). This expression you sent before
range("C:C").name="mycolumn"
I assumed bound mycolumn name to column C no matter what was in column
C and if you inserted a new column before C that became column C then
mycolumn would be bound to that. And I do not understand why that is
not the case from reading this but I take your word for it.
In your new examples much confusion. What is the value of a defined name?
Is the other method where you say if only using from VBA equivalent to this
Range("C:C").Name = "mycolumn" and then use mycolumn as the data
reference for the
cells in column C?
Howard
(thanks again and I promise this is the last question and not to be
your VBA stalker)
On Mon, Mar 19, 2012 at 1:48 PM, Asa Rossoff < <mailto:a...@lovetour.info>
a...@lovetour.info> wrote:
> I think I misunderstood your need. I thought you wanted the name to stay
> with the data, allowing you to insert columns and have the name still
refer
> to the same data. This method does that.
>
>
>
> If you want the name to stick to the column/range reference without regard
> to inserted columns (always column C no matter what, i.e.), and you will
> only use the name from VBA, then you could use a string variable:
>
> Dim MyColumnAddress As String, MyColumn As Range
>
> MyColumnAddress = "C:C"
>
> Set MyColumn = Range(MyColumnAddress) ' After the Set command the
range
> WILL be effected by inserted columns; so re-set just before use if columns
> could have been added/deleting since the last Set command.
>
>
>
> If you want to use a defined name, this will always refer to column C:
>
> Names.Add "MyColumn","=INDIRECT(""C:C"")"
>
>
>
> Hope this helps!
>
> Asa
>
>
>
> -----Original Message-----
> From: tangledweb [ <mailto:domainqu...@gmail.com>
mailto:domainqu...@gmail.com]
> Sent: Monday, March 19, 2012 11:58 AM
> To: Asa Rossoff
> Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
> column names for looping
>
>
>
> thanks, sure wish there was a way to have it be position relative so
>
> could insert new columns without affecting the name on column C but
>
> does not appear to be the case
>
>
>
> On Mar 18, 5:19 pm, "Asa Rossoff" < <mailto:a...@lovetour.info>
a...@lovetour.info> wrote:
>
>> You can name the entire column as well;
>
>>
>
>> range("C:C").name="mycolumn" ' create workbook-level name
>
>>
>
>> then reference it as:
>
>>
>
>> range("mycolumn")
>
>>
>
>> also works as/in a cell formula:
>
>>
>
>> { =mycolumn }
>
>>
>
>> =match("findthis",mycolumn,0)
>
>>
>
>> evaluate("mycolumn") ' formula eval from vba
>
>>
>
>> [mycolumn] ' formula eval from vba
>
>>
>
>> Asa
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
