Nesting For Loops with Arrays to assign variable

Question

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

Shouldn't you be copying the first row from a template worksheet? In `Sheets(i).Cells(1, 1).EntireRow.Copy` the `i` should probably be 1. — , Jul 13 '18 at 00:31
that is correct. I actually had that as 1 and changed it thinking I was typing too fast. — Scott, Jul 13 '18 at 00:34
Not related to the answer but your setup looks like it could hugely benefit from a Dictionary object to keep your arrays in a neater order. — jamheadart, Jul 13 '18 at 00:36
I'll definitely look into Dictionary. New territory for me. But yeah, it's pretty obnoxious in it's current state. — Scott, Jul 13 '18 at 00:43

David Zemens · Accepted Answer · 2018-07-13T18:35:25.150

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.

Thank you @davidZemens! This is amazing. I appreciate the education into Dictionaries. — Scott, Jul 13 '18 at 20:27

Nesting For Loops with Arrays to assign variable

1 Answers1