时间戳

首页 > 解决方案 > 使用数组嵌套 For 循环以分配变量

arrays - 使用数组嵌套 For 循环以分配变量

问题描述

我正在尝试将一些现有代码压缩到另一个现有循环中的数组中。

1）基于数组（TerrArray）在工作簿中创建新工作表。

2）以相同的方式格式化所有新工作表。

3) 为每个新创建的工作表声明一个单独的 lastrow 变量。

Dim Counter As Long
Dim TerrArray
Dim LongArray
Dim NAlr As Long, AUlr As Long, BRlr As Long, CAenlr As Long, CAfrlr As Long, DElr As Long, ESlr As Long, FRlr As Long, ITlr As Long, MXlr As Long, USAlr As Long, UK As Long, r As Long
TerrArray = Array("NA", "AU", "BR", "CAen", "CAfr", "DE", "ES", "FR", "IT", "MX", "USA", "UK")
LongArray = Array(LR, NAlr, AUlr, BRlr, CAenlr, CAfrlr, DElr, ESlr, FRlr, ITlr, MXlr, USAlr, UKlr)

''' Create sheets

       For m = 0 To UBound(TerrArray)
          Sheets.Add(After:=ActiveSheet).Name = TerrArray(m)
       Next m

''' Loops formatting of each sheet

     Counter = Sheets.Count
       For i = 2 To Counter

          Sheets(1).Cells(1, 1).EntireRow.Copy
          Sheets(i).Cells(1, 1).PasteSpecial
          [other formatting code, etc]
       Next i

''' Assigns lastrow variable to each sheet

      NAlr = Sheets("NA").Cells(Rows.Count, "B").End(xlUp).Row
      AUlr = Sheets("AU").Cells(Rows.Count, "B").End(xlUp).Row
      BRlr = Sheets("BR").Cells(Rows.Count, "B").End(xlUp).Row
      [etc]

我不知道将 LongArray 放置在其他两个循环之一的哪个位置以使用类似的东西

  LongArray(n) = Sheets(i).Cells(Rows.Count, "B").End(xlUp).Row

上面的代码执行后，我后面的功能代码可以获取行计数变量。

编辑：不确定我是否朝着正确的方向前进

   For m = 0 To UBound(TerrArray)
       Sheets.Add(After:=ActiveSheet).Name = TerrArray(m)
       LongArray(m) = Sheets(TerrArray(m)).Cells(Rows.Count, "B").End(xlUp).Row
   Next m

标签： arraysexcelvba

解决方案

使用字典（按照该链接查看类似的 SO 问题/答案，我将在其中解释如何使用字典类）：

Const TERR as String ="NA,AU,BR,CAen,CAfr,DE,ES,FR,IT,MX,USA,UK"

Sub foo()
Dim dict = CreateObject("Scripting.Dictionary")
Dim t as Variant
Dim newSheet As Worksheet
For Each t in Split(TERR, ",")
    ' Create each sheet
    Set newSheet = Sheets.Add(After:=ActiveSheet)
    newSheet.Name = t
    ' Formatting stuff:
      Sheets(1).Rows(1).EntireRow.Copy
      newSheet.Cells(1, 1).PasteSpecial
      ' [other formatting code, etc]

    With newSheet
        Sheets(1).Rows(1).EntireRow.Copy
        ' formatting code probably goes here
        dict.Add(t, .Cells(.Rows.Count, 2).End(xlUp).Row)
    End With
Next

' Now, you can reference the last row associated with each
' Instead of using variable NAlr, just refer to the dictionary value associated with the "NA" key:

    MsgBox dict("NA")


    'etc...

或者，只需编写 aFunction来获取最后一行，因为最后一行可能会改变，因此养成直接/静态分配这些值的习惯可能不是一个好主意。

推荐阅读