Loop each sheet between two sheets
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Loop each sheet between two sheets

  1. #21
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Loop each sheet between two sheets

    Arrays coupled with dictionaries are indeed quick
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #22
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    430
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  3. #23
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Loop each sheet between two sheets

    I've added some comments
    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
    For more info on arrays & dictionaries have a look here
    https://excelmacromastery.com/vba-dictionary/
    https://excelmacromastery.com/excel-vba-array/
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #24
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    430
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop each sheet between two sheets

    Bless your kindness Fluff
    i was just gunna stare at it until it made sense
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #25
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Loop each sheet between two sheets

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #26
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,519
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Loop each sheet between two sheets

    Quote Originally Posted by BlakeSkate View Post
    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. #27
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    430
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop each sheet between two sheets

    Quote Originally Posted by Fluff View Post
    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
    your help is immensely appreciated

  8. #28
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default 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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #29
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    430
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop each sheet between two sheets

    thank you for the help again!
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  10. #30
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Loop each sheet between two sheets

    My pleasure
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •