StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>[Edit]</p> <p>This sub will tell you if you have duplicate named ranges in any open workbooks. As DanThMan said, you can refer to it in the <code>ThisWorkbook</code> object module:</p> <pre><code>Sub CheckRangeDups() Dim wb As Workbook Dim nm As Name Dim count As Integer, i As Integer Dim arrNm() As String Dim dup As Boolean Dim rngChk As Range ReDim arrNm(0) 'cycle through workbooks' For Each wb In Workbooks 'cycle through names in workbook' For Each nm In wb.Names 'check if name refers to a range' On Error Resume Next Set rngChk = Nothing Set rngChk = nm.RefersToRange On Error GoTo 0 If Not rngChk Is Nothing Then dup = False 'check if name in array of names' For i = 0 To UBound(arrNm) If nm.Name = arrNm(i) Then MsgBox "Named range " & nm.Name & " duplicated." dup = True Exit For End If Next i 'if not then add it' If Not dup Then arrNm(count) = nm.Name count = count + 1 ReDim Preserve arrNm(count) End If End If Next nm Next wb End Sub </code></pre> <p>[/Edit]</p> <p>If I understand you right, fully qualifying your references to the ranges will solve your problem. Example: With two files open named file1 and file2, name cell A1 on sheet 1 "arange" in both files. In file1 type "file1" into the cell, on file2 type "file2" into the cell. Now run this:</p> <pre><code>Sub whichRange() Dim f1 As Workbook, f2 As Workbook Set f1 = Workbooks("file1.xls") Set f2 = Workbooks("file2.xls") Dim s1 As Worksheet, s2 As Worksheet Set s1 = f1.Worksheets(1) Set s2 = f2.Worksheets(1) Dim r1 As Range, r2 As Range Set r1 = s1.Range("arange") Set r2 = s2.Range("arange") Debug.Print "WB: "; f1.Name; " cell: "; r1.Name; " contents: "; r1 Debug.Print "WB: "; f2.Name; " cell: "; r2.Name; " contents: "; r2 End Sub </code></pre> <p>Fully qualifying allows Excel to know which you are referring to.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload