时间戳

首页 > 解决方案 > 如何将我的 VBA 命名范围转换为循环代码

excel - 如何将我的 VBA 命名范围转换为循环代码

问题描述

在下面的 VBA 代码示例中，我手动创建了 3 个命名范围，其中 ReportID_0XX 从增量从 ...01 增加到 ...03，并且还将 ComparisonReport 单元格引用从 $A$6 增加到 $A$8

这在较小的范围内很好，但现在我需要对 60 个实例执行此操作，有效地继续该过程 57 次以上，以便为“ReportID_001”到“ReportID_060”创建命名范围（添加保留适用的前导零）并让单元格引用开始6 澳元，最终价格为 65 澳元。

我对使用 VBA 命名范围非常陌生。

''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''

ActiveWorkbook.Names.Add Name:="ReportID_001", RefersTo:= _
"='ComparisonReport'!$A$6"
ActiveWorkbook.Names("ReportID_001").Comment = ""

''''''''''''''''''''''''''''''''''

ActiveWorkbook.Names.Add Name:="ReportID_002", RefersTo:= _
"='ComparisonReport'!$A$7"
ActiveWorkbook.Names("ReportID_002").Comment = ""

''''''''''''''''''''''''''''''''''

ActiveWorkbook.Names.Add Name:="ReportID_003", RefersTo:= _
"='ComparisonReport'!$A$8"
ActiveWorkbook.Names("ReportID_003").Comment = ""

标签： excelvbafor-loopnamed-ranges

解决方案

Sub Test()
   Dim xBk As Workbook, xRng As Range, xCel As Range, xCtr As Long, xRep As String

   Set xBk = ActiveWorkbook
   Set xRng = Range("A6:A66")  ' or whatever range you want
   For Each xCel In xRng.Cells
      xCtr = xCtr + 1
      xRep = "ReportID_" & Format(xCtr, "000")   ' Make it "0000" if > 99 Reports
      xBk.Names.Add Name:=xRep, RefersTo:="='ComparisonReport'!$A$" & (xCtr + 5)
      xBk.Names(xRep).Comment = ""
   Next xCel
End Sub

推荐阅读