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]
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
>
>
>
> -----Original Message-----
> From: <mailto:excel-macros@googlegroups.com>
excel-macros@googlegroups.com [ <mailto:excel-macros@googlegroups.com>
mailto:excel-macros@googlegroups.com]
>
> On Behalf Of dguillett1
> Sent: Sunday, March 18, 2012 1:26 PM
> To: <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
> column names for looping
>
> the simplest is
>
> sub nameit
>
> cells(7,4).name="whateveryouwanttonameit"
>
> end sub
>
> =whaeveryounamedit
>
> Don Guillett
>
> Microsoft MVP Excel
>
> SalesAid Software
>
> < <mailto:dguille...@gmail.com> mailto:dguille...@gmail.com>
<mailto:dguille...@gmail.com> dguille...@gmail.com
>
> -----Original Message-----
>
> From: tangledweb
>
> Sent: Sunday, March 18, 2012 5:22 AM
>
> To: MS EXCEL AND VBA MACROS
>
> Subject: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
>
> column names for looping
>
> I am trying to understand how to name a column of data and be able to
>
> reference the data by that name. Ideally I could set the name
>
> programatically but if it needs to be done through the ribbon command
>
> that is acceptable.
>
> Ideally inserting a new column before the named column will not affect
>
> that column's name.
>
> The simplest example of
>
> =========================
>
> Sub Macro1()
>
> Dim x as long
>
> Const ExitValue = 4
>
> x = Cells(6, ExitValue).Value
>
> Cells(7, ExitValue) = x
>
> End Sub
>
> =======================
>
> Functions but for some reason I can not see the value of x in the
>
> immediate window. I just get nothing back if I print x.
>
> But is this really the best wah to do this? It seems inelegant at
>
> best. And certainly if I insert a new column 3 I need to change this
>
> code to
>
> Const ExitValue = 5
>
> --
>
> 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 <
<mailto:excel-macros@googlegroups.com> mailto:excel-macros@googlegroups.com>
> <mailto:excel-macros@googlegroups.com> excel-macros@googlegroups.com
>
> --
>
> 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 <
<mailto:excel-macros@googlegroups.com> mailto:excel-macros@googlegroups.com>
> <mailto:excel-macros@googlegroups.com-> excel-macros@googlegroups.com-
Hide quoted text -
>
> - Show quoted text -
--
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
