GBKEE

¦^´_ 1# Scott090

1. Sub Ex()
   
2. MsgBox Range("test").Address(, , , 1)
   
3. With ThisWorkbook.Names("test")
   
4.    MsgBox "RefersTo " & .RefersTo
   
5.    MsgBox "RefersToLocal " & .RefersToLocal
   
6.    MsgBox "RefersToR1C1 " & .RefersToR1C1
   
7.    MsgBox "RefersToR1C1Local " & .RefersToR1C1Local
   
8.    MsgBox "RefersToRange " & .RefersToRange
   
9. End With
   
10. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# Scott090

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, Rng_address As String, Sh As Worksheet, Name_Range As String
   
4.     With ThisWorkbook
   
5.         For Each Sh In .Sheets
   
6.             Set Rng = Sh.Cells.Find(What:="Test", LookIn:=xlFormulas, LookAt:=xlPart)
   
7.             If Not Rng Is Nothing Then
   
8.                 Rng_address = Rng.Address
   
9.                 Do
   
10.                     Name_Range = Name_Range & vbLf & Rng.Address(, , , 1)
    
11.                     Set Rng = Sh.Cells.FindNext(Rng)
    
12.                 Loop While Rng_address <> Rng.Address
    
13.             End If
    
14.         Next
    
15.         Name_Range = Mid(Name_Range, 2)
    
16.         Name_Range = Replace(Name_Range, "[" & .Name & "]", "")
    
17.         MsgBox Name_Range
    
18.    End With
    
19. End Sub

½Æ»s¥N½X