Loop each sheet between two sheets

[DataBlake]

so i'm trying to loop between sheets in between my sheets "Program Start" and "ID check"
and if hidden sheets matter it would be between "Program Start" and "Master Image"
i.e the loop logic would be:

for each sheet between ProgramStart to IDcheck
'do a thing
else 'do a thing
next sheet

the reason being as these sheets contain the data i work with but the sheets aren't consistant day to day.
so some days it will be vendor1, vendor 3, vendor 4.
other days it will be all 6 vendors etc

any help would be appreciated

 

[Fluff]

Arrays coupled with dictionaries are indeed quick

 

[DataBlake]

i wish i understood this better.
so you set the array length to be all 3 sheets?
you pass the values of the if statement to the unbound of the array?
my brain itches.

 

[Fluff]

I've added some comments

Sub BlakeSkate()
   Dim i As Long, j As Long
   Dim Dic As Object
   Dim Ary As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   For i = Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1
      With Sheets(i)
      If .Visible = xlSheetVisible Then
      'if the sheet is visible creates an array of the currentregion
         Ary = .Range("A1").CurrentRegion.Value2
         'loops through the array start from row 2 (assumes header in row1)
         For j = 2 To UBound(Ary)
         'checks to see if the value is in the dictionary, if not adds the value from cola as the key & the value from col R as the item
            If Not Dic.exists(Ary(j, 1)) Then Dic.Add Ary(j, 1), Ary(j, 18)
         Next j
      End If
      End With
   Next i
   With ActiveSheet
      'creates an array of columns A:D
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
      'loops through the array starting from row 2
      For i = 2 To UBound(Ary)
         'pulls the item from the dictionary associated with the value in col A
         Ary(i, 4) = Dic.item(Ary(i, 1))
      Next i
      'copies the array back to the sheet
      .Range("A1").Resize(UBound(Ary), 4).Value = Ary
   End With
End Sub

For more info on arrays & dictionaries have a look here
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/

 

[DataBlake]

I've added some comments
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/

Bless your kindness Fluff
i was just gunna stare at it until it made sense

 

[Fluff]

You're welcome & thanks for the feedback

 

[mikerickson]

that seems to work effectively
and if i understand correctly onesheet is working as the array?

oneSheet is the loop variable, that is looping through the array VisibleSheets.

 

[DataBlake]

   With ActiveSheet
      'creates an array of columns A:D
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
      'loops through the array starting from row 2
      For i = 2 To UBound(Ary)
         'pulls the item from the dictionary associated with the value in col A
         Ary(i, 4) = Dic.item(Ary(i, 1))
      Next i
      'copies the array back to the sheet
      .Range("A1").Resize(UBound(Ary), 4).Value = Ary
   End With
End Sub

okay so i think i've got most of this understood now.
this will help me immensely with all of my projects if i can fill in the blanks.

i understand the first part up until the part i've quoted.
you no longer need the values from the in between sheets since the important info was added to the dictionary key and dictionary value.
so you are reusing ary to define the array as A:D (so at this executable line everything in D is 0)

 Ary(i, 4) = Dic.item(Ary(i, 1))

then for every row (i) you are reassigning the value of D in the array to the value found in the dictionary?
because the i is the part number that we "are searching for" in the dictionary. its confusing to me why it doesn't refer to the actual key. why does Dic.item(Ary(i, 1) return the value and not the key?
i don't see anything in the link you provided explaining this.

 .Range("A1").Resize(UBound(Ary), 4).Value = Ary

and then this line is also confusing to me because you have it resize to the 4th column and the end of the array (which is the last row), So why isnt the "A1" actually "A2"? because we defined the value of the array with i=2?

putting those two lines under a microscope will help fill in the gaps with the resources you provided me
your help is immensely appreciated

 

[Fluff]

This line

Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2

populates the array with all the values in the sheet from A1:D& last used row in col A.
So when the array is written back to the sheet, it needs to be written to the same cells as it came from, ie starting in A1.

Whilst the loop starts i=2 that is only on the assumption that A1 is a header & should be left alone.

With this line

Ary(i, 4) = Dic.item([COLOR=#ff0000]Ary(i, 1)[/COLOR])

The part in red is the key, so if that key exists in the dictionary it will return the associated item (in this case into Ary(i,4)).
If the key does not exist then it will not return anything.

HTH

 

[DataBlake]

thank you for the help again!

 

[Fluff]

My pleasure