ReferesToRange给出运行时错误'91':
问题描述:
嗨我一直在下面的代码的for循环中获得“变量或块变量未设置”行。有谁能告诉我我哪里出错了吗?谢谢ReferesToRange给出运行时错误'91':
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
答
尝试以下并报告回来。你没有包括一些你的编码,我假设...
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
+0
谢谢,但由于某些原因它仍然给出相同的错误。我已经更详细地更新了这个问题。 – Mo123
答
调整作出和评论。
Option Explicit
Sub wqewtr()
Dim Nm As Name
Dim rng As Range
Dim var As Variant '<~~ for numbers, dates and/or text
Dim wb As Workbook
Set wb = ThisWorkbook
For Each Nm In ThisWorkbook.Names
Debug.Print Nm.Name
'nm could a 'special internal name' that starts with an underscore
'skip over these
If Left(Nm.Name, 1) <> "_" Then
'show the address of the defined name range - could be more than one cell
Debug.Print Nm.RefersToRange.Address
'do not try to assign value to a range object unless that range already has been asinged a cell or cells
'rng = Nm.RefersToRange.Value
'Debug.Print rng
'this fails if Nm is more than a single cell
'var = Nm.RefersToRange.Value
'Debug.Print var
'this guarantees one cell
var = Nm.RefersToRange.Cells(1, 1).Value
Debug.Print var
End If
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
你确定你知道什么是**姓名**是,这是你应该找什么? – Jeeped