Does a workbook contain name?

QB

Board Regular
Joined
Jul 5, 2004
Messages
93
Is there a way in VBA to check whether a name, say "QWERTY" exists in a workbook?

Thanks for your help.

QB
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Board Regular
Joined
Jul 5, 2004
Messages
93
Not so complex. All I'm trying to find out is whether a named range exists in the active workbook.

Thanks

QB
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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.
Code:
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

Board Regular
Joined
Jul 5, 2004
Messages
93
Dan

Many thanks. Just what I was looking for.

Regards

QB
 

Forum statistics

Threads
1,147,681
Messages
5,742,587
Members
423,739
Latest member
plkdti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top