时间戳

首页 > 解决方案 > 如何遍历名称集合并返回对象的大小？

excel - 如何遍历名称集合并返回对象的大小？

问题描述

我正在尝试遍历 Names 集合，并且每个符合特定大小（列）要求的名称都会调整大小（在不同的子中调用）。

当我尝试将返回的名称传递给 Range 时，出现以下错误。

“运行时错误'1004'：对象'_Global'的方法'Range'失败。

Sub colCounts()
    Dim wb as Workbook
    Dim nm as Name
    Dim rng as Range
    Set wb = ActiveWorkbook

    For Each nm in wb.Names
        debug.print nm.Name ' verify loop returning as expected
        Set rng = Range(nm.Name) 'this line throws the error when added
        debug.print nm.Name & " " & rng.Columns.Count
    Next nm
End Sub

我还尝试先将名称传递给字符串：

Dim str as String
.....
str = nm.Name
Set rng = Range(str)

标签： excelvba

解决方案

Sub colCounts()

    Dim wb As Workbook
    Dim nm As Name
    Dim rng As range

    Set wb = ActiveWorkbook

   For Each nm In wb.Names

       Set rng = ThisWorkbook.Names(nm.Name).RefersToRange
       Debug.Print nm.Name & " " & rng.Columns.Count

    Next nm

End Sub

推荐阅读