Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to display defined cell names(Excel 97 SR-2) (Excel 97 SR-2)

    I am using Column B (number format - 2 decimal places) to display a
    list of numbers. Each of these cells in Column B has been assigned a
    cell name using the INSERT->DEFINE->NAME... command from the menu.
    (e.g. $B$2 displays '10.00', and is assigned 'V_1', $B$3 displays '5.00' and
    is assigned 'V_2', etc.)

    I have tried unsuccessfully to create a macro that scans Column B and displays
    each cell name in the adjacent cell in Column A (text format).
    (e.g. $A$2 to display 'V_1', $A$3 to display 'V_2', etc.)

    HOW CAN I MODIFY MY MACRO TO EXTRACT THE CELL NAME PROPERTY
    TO BE USED IN THE MANNER DESCRIBED ABOVE?

    Any help I can get would be greatly appreciated.

    Thanks.


    ================================================== ============
    Note: FYI....
    I am using Column D (number format - 2 decimal places) to display the results
    of some expressions using the numbers from Column B.
    (e.g. $D$2 displays '15.00', and is assigned 'Result_1')
    I am using the adjacent cells in Column C (text format) to display the text expression.
    (e.g. $C$2 displays '=V_1+V_2')
    I am accomplishing this via this command contained within a do..while loop:
    ActiveSheet.Cells(rowIndex, 3).Value = ActiveSheet.Cells(rowIndex, 4).FormulaR1C1

    ================================================== =============

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to display defined cell names(Excel 97 SR-2) (Excel 97 SR-2)

    Jan Karel Pieterse will be able to tell you how to modify this if you have a mixture of local and global names. Here is is simple macro:

    Sub SetNames()
    Dim r As Range
    Dim c As Range
    Dim n As Name
    Dim w As Workbook
    Set r = Range("B1:B37") ' adapt as needed
    Set w = ActiveWorkbook
    ' Loop through cells in range
    For Each c In r
    ' Loop through names in workbook
    For Each n In w.Names
    ' Test if name refers to cell
    If n.RefersToRange = c Then
    ' Enter name in cell to the left
    c.Offset(0, -1) = n.Name
    Exit For
    End If
    Next n
    Next c
    End Sub

Posting Permissions

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