CODEWITHSUNDEEP
arraysexcelvba
Scott
Scott

Reputation: 79

Nesting For Loops with Arrays to assign variable

I'm trying to condense some existing code hopefully into an array within another existing loop.

1) Create new sheets within a workbook based on an array (TerrArray).

2) Format all new sheets the same way.

3) Declare a separate lastrow variable for each newly created sheet.

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]

I can't figure out where to place LongArray in one of the 2 other loops order to use something like

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

After the above code executes, I have functioning code that follows that can pick up the row count variables.

EDIT: Not sure if I'm going in the right direction

   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

Upvotes: 0

Views: 117

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Use a dictionary (follow that link for a similar SO question/answer where I explain how to use the dictionary class):

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...

Or, just write a Function to get the last row, since the last row may change it's probably not a great idea to get in the habit of assigning these values directly / statically.

Upvotes: 1

Related Questions