Does a workbook contain name?

[QB]

Is there a way in VBA to check whether a name, say "QWERTY" exists in a workbook?

Thanks for your help.

QB

 

[HalfAce]

Do you mean to have the name searched for throughout the entire workbook, sheet by sheet, cell by cell?
Will you be looking from within the same workbook the name will be found in providing it's found at all? (As opposed to opening a different workbook and searching through it from the one that contains the code.)

Dan

 

[QB]

Not so complex. All I'm trying to find out is whether a named range exists in the active workbook.

Thanks

QB

 

[HalfAce]

Sure, try one of these and see if it's what you're looking for.

The first one will simply tell you if a range by the name specified exists in that workbook, or not.

Sub DoesThisRangeExist()
Dim Rng As String
Rng = InputBox("What range do you want to search for?", "Search Criteria")

For i = 1 To ActiveWorkbook.Names.Count
  On Error GoTo Nope
    If ActiveWorkbook.Names(i).Name = Rng Then
        MsgBox ("The range named '" & Rng & "' was found in this workbook."), , "Search Sucessful"
        Exit Sub
    End If
Next i
   
Nope:
    MsgBox ("The range named '" & Rng & "' can not be found in this workbook."), , "Search Failed"

End Sub

The second one is pretty much the same, but if the range does exist, it will go to and select the range.

Sub FindThisRange()
Dim Rng As String
Rng = InputBox("What range do you want to search for?", "Search Criteria")

On Error GoTo Nope
Application.GoTo Rng
Exit Sub

Nope:
MsgBox ("The range named '" & Rng & "' can not be found in this workbook."), , "Search Failed"
End Sub

Hope it helps,
Dan

 

[QB]

Dan

Many thanks. Just what I was looking for.

Regards

QB

 

[HalfAce]

Most welcome. Glad it worked for you.