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

嗨，我在以下代码的for循环中的行中不断获取“变量或未设置块变量”。谁能告诉我我要去哪里错了？谢谢

 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

最佳答案

您在代码中有两个问题：

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

关于excel - ReferesToRange给出运行时错误“91”:，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43347301/