+ Reply to Thread
Results 1 to 3 of 3

VBA copy multiple lines within For Loop

Hybrid View

  1. #1
    Registered User
    Join Date
    10-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    VBA copy multiple lines within For Loop

    How would you go about printing information on a second line while still in the same "a" in the for loop?

    Referencing the below code, away_ml should be printed in (4,5), but home_ml should be in (5,5). Currently, the code I have prints the first line correctly, but then does not print the "a+1" like I am intending.

    I don't think I can use the Step method in the intial for loop line (For a=4 to g step 2) as I would miss rows that I need to pull information from.

    Uploaded is an example file with it correctly copied on the right hand side of the sheet. Below is my current code.

    Sub Simulate()
    
    a = 4
    g = Sheet3.Cells(Rows.Count, "B").End(xlUp).Row
    
    Sheet3.Range("K4:BF101").ClearContents
    
    Dim away_team As String
    Dim home_team As String
    Dim away_ML As Long
    Dim home_ML As Long
    Dim away_line As String
    Dim home_line As String
    Dim loca As String
    
    
    For a = 4 To g
    
        away_team = Sheet3.Cells(a, 7).Value
        home_team = Sheet3.Cells(a, 8).Value
        away_ML = Sheet3.Cells(a, 4).Value
        home_ML = Sheet3.Cells(a, 5).Value
        home_line = Sheet3.Cells(a, 3).Value
        away_line = home_line * -1
        loca = Sheet3.Cells(a, 6).Value
        
        If loca = "H" Then
            
            Sheet3.Cells(a, 11).Value = Sheet3.Cells(a, 2).Value & "A"
            Sheet3.Cells(a + 1, 11).Value = Sheet3.Cells(a, 2).Value & "B"
            Sheet3.Cells(a, 12).Value = "A"
            Sheet3.Cells(a + 1, 12).Value = "H"
            Sheet3.Cells(a, 13).Value = away_line
            Sheet3.Cells(a + 1, 13).Value = home_line
            Sheet3.Cells(a, 14).Value = away_ML
            Sheet3.Cells(a + 1, 14).Value = home_ML
            Sheet3.Cells(a, 15).Value = away_team
            Sheet3.Cells(a + 1, 15).Value = home_team
            Sheet3.Cells(a, 16).Value = home_team
            Sheet3.Cells(a + 1, 16).Value = away_team
        
        ElseIf loca = "N" Then
        
            Sheet3.Cells(a, 11).Value = Sheet3.Cells(a, 2).Value + "A"
            Sheet3.Cells(a + 1, 11).Value = Sheet3.Cells(a, 2).Value + "B"
            Sheet3.Cells(a, 12).Value = "N"
            Sheet3.Cells(a + 1, 12).Value = "N"
            Sheet3.Cells(a, 13).Value = away_line
            Sheet3.Cells(a + 1, 13).Value = home_line
            Sheet3.Cells(a, 14).Value = away_ML
            Sheet3.Cells(a + 1, 14).Value = home_ML
            Sheet3.Cells(a, 15).Value = away_team
            Sheet3.Cells(a + 1, 15).Value = home_team
            Sheet3.Cells(a, 16).Value = home_team
            Sheet3.Cells(a + 1, 16).Value = away_team
            
        End If
             
        
    Next a
    
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: VBA copy multiple lines within For Loop

    You should use another variable for the destination row as it's no always the same as the source row.
    Sub Simulate()
    Dim away_team As String
    Dim home_team As String
    Dim away_ML As Long
    Dim home_ML As Long
    Dim away_line As String
    Dim home_line As String
    Dim loca As String
    Dim dstRow As Long
    
        g = Sheet3.Cells(Rows.Count, "B").End(xlUp).Row
    
        Sheet3.Range("K4:BF101").ClearContents
    
        dstRow = 4
    
        For a = 4 To g
    
            away_team = Sheet3.Cells(a, 7).Value
            home_team = Sheet3.Cells(a, 8).Value
            away_ML = Sheet3.Cells(a, 4).Value
            home_ML = Sheet3.Cells(a, 5).Value
            home_line = Sheet3.Cells(a, 3).Value
            away_line = home_line * -1
            loca = Sheet3.Cells(a, 6).Value
    
            Sheet3.Cells(dstRow, 11).Value = Sheet3.Cells(a, 2).Value & "A"
            Sheet3.Cells(dstRow + 1, 11).Value = Sheet3.Cells(a, 2).Value & "B"
    
            If loca = "H" Then
                Sheet3.Cells(dstRow, 12).Value = "A"
                Sheet3.Cells(dstRow + 1, 12).Value = "H"
            ElseIf loca = "N" Then
                Sheet3.Cells(dstRow, 12).Value = "N"
                Sheet3.Cells(dstRow + 1, 12).Value = "N"
            End If
    
            Sheet3.Cells(dstRow, 13).Value = away_line
            Sheet3.Cells(dstRow + 1, 13).Value = home_line
            Sheet3.Cells(dstRow, 14).Value = away_ML
            Sheet3.Cells(dstRow + 1, 14).Value = home_ML
            Sheet3.Cells(dstRow, 15).Value = away_team
            Sheet3.Cells(dstRow + 1, 15).Value = home_team
            Sheet3.Cells(dstRow, 16).Value = home_team
            Sheet3.Cells(dstRow + 1, 16).Value = away_team
    
            dstRow = dstRow + 2
    
        Next a
    
    End Sub
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-23-2020
    Location
    USA
    MS-Off Ver
    365
    Posts
    16

    Re: VBA copy multiple lines within For Loop

    Quote Originally Posted by Norie View Post
    You should use another variable for the destination row as it's no always the same as the source row.
    Sub Simulate()
    Dim away_team As String
    Dim home_team As String
    Dim away_ML As Long
    Dim home_ML As Long
    Dim away_line As String
    Dim home_line As String
    Dim loca As String
    Dim dstRow As Long
    
        g = Sheet3.Cells(Rows.Count, "B").End(xlUp).Row
    
        Sheet3.Range("K4:BF101").ClearContents
    
        dstRow = 4
    
        For a = 4 To g
    
            away_team = Sheet3.Cells(a, 7).Value
            home_team = Sheet3.Cells(a, 8).Value
            away_ML = Sheet3.Cells(a, 4).Value
            home_ML = Sheet3.Cells(a, 5).Value
            home_line = Sheet3.Cells(a, 3).Value
            away_line = home_line * -1
            loca = Sheet3.Cells(a, 6).Value
    
            Sheet3.Cells(dstRow, 11).Value = Sheet3.Cells(a, 2).Value & "A"
            Sheet3.Cells(dstRow + 1, 11).Value = Sheet3.Cells(a, 2).Value & "B"
    
            If loca = "H" Then
                Sheet3.Cells(dstRow, 12).Value = "A"
                Sheet3.Cells(dstRow + 1, 12).Value = "H"
            ElseIf loca = "N" Then
                Sheet3.Cells(dstRow, 12).Value = "N"
                Sheet3.Cells(dstRow + 1, 12).Value = "N"
            End If
    
            Sheet3.Cells(dstRow, 13).Value = away_line
            Sheet3.Cells(dstRow + 1, 13).Value = home_line
            Sheet3.Cells(dstRow, 14).Value = away_ML
            Sheet3.Cells(dstRow + 1, 14).Value = home_ML
            Sheet3.Cells(dstRow, 15).Value = away_team
            Sheet3.Cells(dstRow + 1, 15).Value = home_team
            Sheet3.Cells(dstRow, 16).Value = home_team
            Sheet3.Cells(dstRow + 1, 16).Value = away_team
    
            dstRow = dstRow + 2
    
        Next a
    
    End Sub
    This worked perfectly - thanks for the insight!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Print out multiple lines in a textbox via a loop
    By hoi2you in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2020, 08:57 AM
  2. Replies: 13
    Last Post: 10-01-2019, 05:07 PM
  3. [SOLVED] Loop and copy every x lines of range
    By rpinxt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2018, 05:53 AM
  4. [SOLVED] Loop through lines in a text file ignoring lines between two different strings
    By webwyzard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2018, 08:00 PM
  5. [SOLVED] Loop and delete lines till no more lines left to check
    By rpinxt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2018, 08:15 AM
  6. loop copy paste and average of multiple lines
    By cboseva in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-19-2018, 06:20 PM
  7. [SOLVED] Creating a Macro Loop to copy & paste several lines
    By homike2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 07:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1