excel - ReferesToRange 给出运行时错误 '91' :

Question

嗨，我在以下代码的 for 循环中不断收到“变量或未设置 block 变量”的信息。谁能告诉我哪里出错了？谢谢

 Public Sub TestFind()

 Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))


  wsNew.Range(wsNew.Cells(1, 1), wsNew.Cells(1, 17)).Value _
             = Array("ReturnId", "GridName", "Item", "TabName", "AltFldName", "FieldPos", "Reference", "Type", _
             "SortPos", "FieldSize", "CalcField", "CellDesc", "DoNotExport", "SortStrategy", "Threshold", "IsInnerGridCell", "ReportLine")

 Dim Nm As Name
 Dim rng As Range
 Dim wb As Workbook

 Set wb = ThisWorkbook
 For Each Nm In ThisWorkbook.Names
     rng = Nm.RefersToRange.Value
 Next

 End Sub

Answer 1

您的代码中有两个问题:

1- 并非所有名称都一定指命名范围。例如，它们可能指的是常量。因此，在假设名称确实引用一个范围之前，您需要添加一些检查。

2-要将范围对象分配给命名范围，您需要使用Set

试试这个:

Dim Nm As Name, rng As Range
For Each Nm In ThisWorkbook.Names
    Debug.Print Nm.Name
    On Error Resume Next
    Set rng = Nm.RefersToRange ' <-- Use Set to assign object references
    If Err.Number <> 0 Then GoTo NextNm ' <-- This name does not refer to a named range
    On Error GoTo 0
    Debug.Print rng.Address
   ' ... Do whatever you want with the named range
NextNm:
    On Error GoTo 0
Next Nm