如何查找在Excel中使用某个命名范围的位置？

Sub Find_namedrange_place() Dim xRg As Range Dim xCell As Range Dim xSht As Worksheet Dim xFoundAt As String Dim xAddress As String Dim xShName As String Dim xSearchName As String On Error Resume Next xShName = Application.InputBox("Please type a sheet name you will find cells in:", "Kutools for Excel", Application.ActiveSheet.Name) Set xSht = Application.Worksheets(xShName) Set xRg = xSht.Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not xRg Is Nothing Then xSearchName = Application.InputBox("Please type the name of named range:", "Kutools for Excel") Set xCell = xRg.Find(What:=xSearchName, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not xCell Is Nothing Then xAddress = xCell.Address If IsPresent(xCell.Formula, xSearchName) Then xFoundAt = xCell.Address End If Do Set xCell = xRg.FindNext(xCell) If Not xCell Is Nothing Then If xCell.Address = xAddress Then Exit Do If IsPresent(xCell.Formula, xSearchName) Then If xFoundAt = "" Then xFoundAt = xCell.Address Else xFoundAt = xFoundAt & ", " & xCell.Address End If End If Else Exit Do End If Loop End If If xFoundAt = "" Then MsgBox "The Named Range was not found", , "Kutools for Excel" Else MsgBox "The Named Range has been found these locations: " & xFoundAt, , "Kutools for Excel" End If On Error Resume Next xSht.Range(xFoundAt).Select End If End Sub Private Function IsPresent(sFormula As String, sName As String) As Boolean Dim xPos1 As Long Dim xPos2 As Long Dim xLen As Long Dim I As Long xLen = Len(sFormula) xPos2 = 1 Do xPos1 = InStr(xPos2, sFormula, sName) - 1 If xPos1 < 1 Then Exit Do IsPresent = IsVaildChar(sFormula, xPos1) xPos2 = xPos1 + Len(sName) + 1 If IsPresent Then If xPos2 <= xLen Then IsPresent = IsVaildChar(sFormula, xPos2) End If End If Loop End Function Private Function IsVaildChar(sFormula As String, Pos As Long) As Boolean Dim I As Long IsVaildChar = True For I = 65 To 90 If UCase(Mid(sFormula, Pos, 1)) = Chr(I) Then IsVaildChar = False Exit For End If Next I If IsVaildChar = True Then If UCase(Mid(sFormula, Pos, 1)) = Chr(34) Then IsVaildChar = False End If End If If IsVaildChar = True Then If UCase(Mid(sFormula, Pos, 1)) = Chr(95) Then IsVaildChar = False End If End If End Function

Kutools for Excel - 包括 300 多个方便的 Excel 工具。 全功能免费试用 30-天，无需信用卡！ 立即免费试用！

Kutools for Excel - 包括 300 多个方便的 Excel 工具。 全功能免费试用 30-天，无需信用卡！ 立即行动吧!

将小时转化为分钟 Kutools for Excel!

准备好增强您的 Excel 任务了吗？ 利用的力量 Kutools for Excel - 您终极的节省时间的工具。 简化复杂的任务并像专业人士一样浏览数据。 以闪电般的速度体验 Excel！

为什么需要 Kutools for Excel

🛠️  超过 300 项强大功能: Kutools 包含 300 多项高级功能，可简化您在 1500 多种场景中的工作。

📈  卓越的数据处理能力：合并单元格、删除重复项并执行高级数据转换 - 所有这些都不费吹灰之力！

⏱️  高效的批量操作：当你可以聪明地工作时，为什么还要付出额外的努力呢？ 轻松批量导入、导出、组合和调整数据。

📊  可定制的图表和报告：访问各种附加图表并生成富有洞察力的报告。

🗄️  强大的导航窗格：通过强大的列管理器、工作表管理器和自定义收藏夹获得优势。

📝  七种类型的下拉列表：通过各种功能和类型的下拉列表使数据输入变得轻而易举。

🎓  用户友好：对于初学者来说轻而易举，对于专家来说是一个强大的工具。

立即下载，与 Excel 一起穿越时空！

Office Tab 为 Office 带来选项卡式界面，让您的工作更轻松