# Thread: Loop each sheet between two sheets Thanks: 0 Likes:  2 Post #5328861 (1)Post #5328254 (1)

1. ## Re: Loop each sheet between two sheets

Arrays coupled with dictionaries are indeed quick

2. ## Re: Loop each sheet between two sheets

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.

3. ## Re: Loop each sheet between two sheets

Code:
```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```
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/

4. ## Re: Loop each sheet between two sheets

i was just gunna stare at it until it made sense

5. ## Re: Loop each sheet between two sheets

You're welcome & thanks for the feedback

6. ## Re: Loop each sheet between two sheets

Originally Posted by BlakeSkate
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.

7. ## Re: Loop each sheet between two sheets

Originally Posted by Fluff
Code:
```   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)

Code:
` 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.

Code:
` .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

8. ## Re: Loop each sheet between two sheets

This line
Code:
`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
Code:
`Ary(i, 4) = Dic.item(Ary(i, 1))`
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

9. ## Re: Loop each sheet between two sheets

thank you for the help again!

My pleasure