Results 1 to 4 of 4

Thread: Using For Each with Multiple Ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using For Each with Multiple Ranges

    Hi All,

    I am working on some code that will open two files, and copy information from one file to the other.

    I've got a For Each set up which finds the names of the files, opens them and then formats as I need. The issue I am coming across arises when I move to the next cell down - it only moves down within one of the ranges, and I cant figure out how to get it to move down on both. I suspect its how I've written the For Each command, but any help is greatly appreciated.

    Code:
    Sub ActionPlanAmends()
        Dim xpathname As String, xpathname2 As String
        Dim rng As Range
        Dim rng2 As Range
        Dim PreviousFileName As String
        Dim CurrentFileName As String
        Dim PreviousFile As Workbook
        Dim CurrentFile As Workbook
        
                xpathname = "C:\Users\sgeoghegan\Desktop\Test\"
                xpathname2 = "C:\Users\sgeoghegan\Desktop\Self Assessments\H2\"
                Set rng = Sheets("List of Names").Range("B1:B57")
                Set rng2 = Sheets("List of Names").Range("A1:A57")
                     
            For Each R2 In rng2
                CurrentFileName = R2.Value
                Set CurrentFile = Workbooks.Open(xpathname2 & CurrentFileName & ".xlsm")
                
            For Each r In rng
                 PreviousFileName = r.Value
                 Set PreviousFile = Workbooks.Open(xpathname & PreviousFileName & ".xlsm")
        
            'Formatting Code Here
    
     
    
    Next r
    Next R2
        
    End Sub
    When the code moves to 'Next r' it does as I expect, but the r2 above is being ignored, presumably because of how the For Each has been set up.

    Thanks,
    Simon
    Last edited by SimonGeoghegan; Jul 17th, 2019 at 06:26 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,201
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using For Each with Multiple Ranges

    How about
    Code:
       Set Rng2 = Sheets("List of Names").Range("A1:A57")
       
       For Each r2 In Rng2
          CurrentFileName = r2.Value
          Set CurrentFile = Workbooks.Open(xpathname2 & CurrentFileName & ".xlsm")
          
          PreviousFileName = r2.Offset(, 1).Value
          Set PreviousFile = Workbooks.Open(xpathname & PreviousFileName & ".xlsm")
          
          'Formatting Code Here
       Next r2
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Nov 2013
    Location
    Manchester
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using For Each with Multiple Ranges

    Thanks Fluff, that worked perfectly - I thought I had to have 2 separate For Each in operation because I was working off two lists. Lesson learnt!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,201
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using For Each with Multiple Ranges

    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

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
  •