Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    naming cells with a macro (excel 2002 sp-2)

    I'm still a vba beginner.
    I would like to create a macro in order to use some text already typed inside of a cell, and to name its neigbour-cell with this text as their name.

    Without vba, the command Insert >> define >> name automatically retrieve this text: I hit the return key and the cell is named as I want.

    I have a ton of cells to name this way... Each one of course with a different name (listed in a column).
    My worksheet already contains the cells with the text to be used as name for their neighbour.

    If I create a macro registering my action about one cell, the macro just repeats literally this single action: using specifically (between quotes) the name of this cell, and its absolute address as follows:

    ActiveWorkbook.Names.Add Name:="dummy_name", RefersToR1C1:="=Sheet1!R4C3"

    In this example, the text dummy_name was typed in cell B4 and the named cell was in C4 (row 4, column 3 --> R4C3)

    I'm trying to build a generic code to be run with anyone of my filled cells and using it to name a different cell each time...

    Here is my unsuccessful attempt:

    Dim MyString As String

    ActiveCell.Offset(0, 0).Range("A1").Select

    MyString = ActiveCell.Value
    <font color=448800>'I try to give as value to MyString the content of the cell containing the text to be used as name</font color=448800>

    ActiveCell.Offset(0, 1).Range("A1").Select
    <font color=448800>'I move the selection to the cell to be named</font color=448800>

    ActiveWorkbook.Names.Add Name:=MyString, RefersToR1C1:=Selection.Address


    This obviously does not work...

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Try

    ActiveCell.Offset(0, 1).Name = ActiveCell.Text

    As range names cannot include blank spaces you may wish to handle that eventuality. For Excel 2000 and later the followng might suffice :

    ActiveCell.Offset(0, 1).Name = Replace(ActiveCell.Text, " ", "_")

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Andrew, it works like a charm

    I am happy again!

    But how frustrating it is to get a one short line brillant solution after a painful page of explanations... :-)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Although it is a good idea to use the macro recorder when learning VBA, it rarely provides the most efficient code. You should also explore Excels Object Mode (from VBA press F2) and examine the properties and methods of the various objects such as Workbook, Worksheets, Ranges etc..

    Andrew

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    I just opened the "Object Browser" window (pressing F2 in VBA window) for the very first time...

    I confess I'm feeling more lost than helped!
    What I understand is that there is the following hierarchy: Library / Classes / Members / Properties or Functions

    I do not understand where the "mode" and "Method" you're mentionning may apply...

    I see on the left top two small textboxes allowing me to search but I'm not sure how to use them.
    I have a limited list of values in the upper one, I chose "excel"
    Then I tested various key words in the second one

    Each time I run the search, I get plenty lists:
    - The top ones in the respective top areas of "class", and "member" in a non alphabetical order
    - The bottom ones in the areas of "Classes" and "members of {the item selected in class(es)}"

    I'm sure this information may help someone but in my case and about the .name method we used above, I cannot see how... [img]/forums/images/smilies/sad.gif[/img]

    I was more than confused when I observed that the porperty CodeName may apply for "workbook" and "worksheet" without any comment explaining the difference of use between them.

    In my case, what I found the closest to my request (in my first post) is the function "createNames", member of the "classes" Range.

    The info I get for this function in the very bottom window is:
    " Function CreateNames([Top], [Left], [Bottom], [Right])
    Member of Excel.Range"

    I sincerely do not know how to use or understand this info ([Top], [Left], [Bottom], [Right])?

    Well, do'nt be afraid, I'm not either asking you all these explanations...

    I just wanted to share how lost one can be in this window... and how useful the lounge (and by the way yourself) are for a newbee like me.

    Thanks again

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Dominik,

    Sorry about a misprint - Mode should have reead Model.

    Once you have the Object browser open, you can select a Class or Member and then press F1, which will launch Help giving a descripition of the item selected and some examples. You will also get links to more help on the relevant properties and methods, which John explained nicely above.

    It may seem daunting at first but with a little experimentation you will find you way around.

    Andrew

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Dominik, the questions you are asking cover a pretty wide range of VBA, so here are my informal definitions (there are some oversimplifications):

    Object: a part of the application that is identifiable as a unit. Objects have a hierachy, starting at the Application, of child grandchild, etc., sub-objects. For example each of these is a successively more detailed Excel object and sub-object: Application, WorkBook, Worksheet, Range, Formula (a single cell is a range).

    Property: a value held by an Object. Many Objects have a Name Property. Some Properties can't be changed (read only), some can.

    Method: a way of changing the Properties of an Object. Many Objects have a Delete Method.

    Event: certain things that a user may do are 'trapped' by the application and can be used to trigger code. Events are such things as Opening or Closing a Workbook, Printing, Clicking a Cell.

    Generally, that will get you started. See attached graphic. If you intend to get serious about VBA for Excel, it's best to buy a book and work your way through it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    John, Andrew, thousand thanks to both of you for your efforts, your time and all the info displayed!
    How nice the selection of the screenshot displaying all the kind of icons!

    I did my best to "logically organize" in my mind on one hand, what you said me here, the solution of my problem + the vba help overview, and on the other hand, my own attempts through excel vba help...

    Actually I wanted to discover through the excel help the solution Andrew provided me with, i.e.: ActiveCell.Name=ActiveCell.Text and especially the first part ActiveCell.Name

    At the beginning , I was despairing with the Object Browser, because I was using "activecell" in the second research textbox instead of "range"

    Hitting the F1 key informed me that the ActiveCell property was returning a range... I finally found out that "the name of a range object is a name object" and understood better this poetic assertion thanks to the chart of the Excel Object model <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Sorry being so long. It was just to inform you that your whole input was worth of reading and that I used (and will use) each and every bit of it.

    You cannot figure my pleasure when I was looking at my cursor jumping from cell to cell, naming my little cells. (Yes, I know the screenupdating=false thing, but In was too happy to see it at work (enjoying the time already saved)...

    Thanks again

  9. #9
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    There is a really good article on the Word MVP site called "Getting to grips with VBA basics in 15 minutes"

    http://www.mvps.org/word/FAQs/MacrosVBA/VB...icsIn15Mins.htm

    I often point people new to VBA to this article and they all say it is great!

    Belinda

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: naming cells with a macro (excel 2002 sp-2)

    Of course any repetitive task is a good one to start learning VBA.

    But this particular task can be done without VBA:

    - Select both columns (the one with the names and the one that should be named)
    - choose Insert, name, create.
    - check the appropriate boxes.

    Once you have created many names, managing them can be simplified by downloading my Name Manager from the Excel MVP page below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •