names1

Joe100
12-23-2004, 08:49 AM
I have a workbook with 3 worksheets. The worksheets are pageone, pagetwo and nms. I wrote some code to list the names in the workbook in the nms worksheet. There is a name in the workbook called faxno. After I run the code it will list this name twice, even though there is only one name faxno in the workbook. It will list it as faxno then pageone!faxno. Can I modify the program so that it will only list faxno?

VBA:

Public sub nmsinwb()
dim r as integer
dim nm as name
r = 1
For each nm in Names
Cells(r,1).value = name
Cells(r,2).value = value
r=r+1
Next
End Sub

Joe100
12-23-2004, 09:01 AM
The following code prints the names in the workbook on the nms worksheet. How can I modify the following code so that it will print the names in the order that they occur in the workbook?

VB:

Public sub nmsinwb()
dim r as integer
dim nm as name
r = 1
For each nm in Names
Cells(r,1).value = name
Cells(r,2).value = value
r=r+1
Next
End Sub

rick_deacha
12-23-2004, 09:31 AM
A little change and its working 100% ;)Public Sub nmsinwb()
Dim r As Integer
Dim nm As Name

r = 1

For Each nm In ActiveWorkbook.Names
Cells(r, 1).Value = nm.Name
Cells(r, 2).Value = nm.Value
r = r + 1
Next
End Sub You have to lookup in the names inside your workbook... and then the Name and Value should be from that specific Named Range :D

Joe100
12-23-2004, 11:11 AM
Thank you for trying to answer my question, but I guess I should rephrase the question. The code results in the names being printed in their index order. I want them to print out in the order that they occur in the workbook. All the names for worksheet pageone would print out before worksheet page 2. On worksheet pageone the name for cell a1 would print before the name for cell a2. The name for b1 would print before b2 and so forth. Each of the names refers to only one cell.

rick_deacha
12-23-2004, 11:19 AM
How about this... I hardly recomend you to search for Name, Range Objects in the help filesPublic Sub nmsinwb()
Dim r As Integer
Dim nm As Name

r = 1

For Each nm In ActiveWorkbook.Names
Cells(r, 1).Value = nm.Name
Cells(r, 2).Value = nm.Value
Cells(r, 3).Value = Range(nm).Address
Cells(r, 4).Value = Range(nm).Worksheet.Name
r = r + 1
Next

' Do some sort for column D and then for column C
End Sub

herilane
12-23-2004, 11:28 AM
Why not simply sort the range with the results?
Oops... Rick just said that, didn't he... looks like I forgot to Refresh before posting.

Joe100
12-23-2004, 12:59 PM
Thank you for your response.

The line of code

vba:

Cells(r,3).value = Range(nm).address

end vba

results in error 1004 Method range of object global failed.

Kluz
12-23-2004, 10:39 PM
Is it possible that a name in the workbook refers to something other than a range of cells?
When you get this error click on the 'Debug' button (this will put you into 'Break Mode'). Now from the View menu select: Locals Window, in the window that opens click on the + sign in a box next to the nm variable and a listing of properties of the object presently held in the nm variable will open up. From this list it might be possible to deduce what type of object nm is and why you can't get an address from it.

Joe100
12-24-2004, 06:30 AM
I found a solution. I added the following code

vb:

cells(r,3).value = nm


This gave me a column of addresses for the names. I then sorted these.

On another matter since when I type in nm followed by a period, address is not one of the options that come up. Therefore as far as I understand address is not a property of name.

italkid
12-24-2004, 07:05 AM
An other approach might be :

Dim Nm As Name, i As Integer

i = 1

For Each Nm In ThisWorkbook.Names
'Resume next in case name doesn't refer to a range
On Error Resume Next
'Will return the sheet name
Cells(i, 1).Value = Nm.RefersToRange.Parent.Name
'Will return the range address
Cells(i, 2).Value = Nm.RefersToRange.Address
On Error GoTo 0
i = i + 1
Next Nm

:)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum