Results 1 to 3 of 3

VBA copy multiple lines within For Loop

Threaded View

wabrown90 VBA copy multiple lines... 12-03-2020, 12:41 PM
Norie Re: VBA copy multiple lines... 12-03-2020, 01:01 PM
wabrown90 Re: VBA copy multiple lines... 12-03-2020, 01:20 PM
  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

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