Results 1 to 2 of 2
2003-04-22, 20:56 #1
- Join Date
- Mar 2001
- 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.
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
2003-04-22, 21:19 #2
- Join Date
- Mar 2002
- 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:
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