Consulting

Results 1 to 2 of 2

Thread: Help duplicating from one cell onto multiple rows

  1. #1

    Help duplicating from one cell onto multiple rows

    Hi, first post here, hope i have done the process correctly....

    I have been trying to figure out the best way to do this for a few days but keep getting stumped.

    I have a data set from a sports competition whereby each row specifies all data from one match (date, day, time, venue, home team, away team etc). The end result i want to get to is to be able to call/filter for how many days rest a team has had between games. To get to this i have decided to split each game to two rows - row one for home team with all info and row two for away team with all info. This way i am able to create a formula to count days between dates since team last appeared in the "team" column.

    The problem i am having now is trying to create a macro which enables me to loop through the data set and print the information that pertains to both teams onto consecutive rows. For example - the date value in "2022 data" cell A2 should print on "2022 format" cells A2 (home) and A3 (away). Then the date value in A3 should print on new sheet in cells A4 and A5 etc etc. I have managed to get it to work but only for one iteration - it then overwrites itself as i dont know how to make it print from the next empty row. The order in which everything happens is obviously not important, just that the information ends up in the right spot.

    Hope someone can help! Thanks a bunch - pics and code below

    dataset.jpg

    Sub formatdata()
    
    
    
    'Define Start and End Lines for our For Loop
        start_line = 2
        end_line = Sheets("2022 data").Cells(Rows.Count, "A").End(xlUp).Row
        
    'clear contents
    
    
        Sheets("2022 format").Range("A2:M203").ClearContents
        
    'define line to start printing on format sheet
        new_line = 2
    
    
    'For loop to run through 2022 data
        For start_line = 2 To 3
        
        'define variables on 2022 data sheet
        home_date = Seets("2022 data").Cells(start_line, 1).Value
        away_date = Sheets("2022 data").Cells(start_line, 1).Value
        home_day = Sheets("2022 data").Cells(start_line, 2).Value
        away_day = Sheets("2022 data").Cells(start_line, 2).Value
        home_time = Sheets("2022 data").Cells(start_line, 3).Value
        away_time = Sheets("2022 data").Cells(start_line, 3).Value
        home_team = Sheets("2022 data").Cells(start_line, 4).Value
        away_team = Sheets("2022 data").Cells(start_line, 5).Value
        venue = Sheets("2022 data").Cells(start_line, 6).Value
        home_score = Sheets("2022 data").Cells(start_line, 7).Value
        away_score = Sheets("2022 data").Cells(start_line, 8).Value
        
        
        
        Sheets("2022 format").Cells(start_line, 1).Value = home_date
        Sheets("2022 format").Cells(start_line, 2).Value = home_day
        Sheets("2022 format").Cells(start_line, 3).Value = home_time
        Sheets("2022 format").Cells(start_line, 5).Value = home_team
        Sheets("2022 format").Cells(start_line, 6).Value = venue
        Sheets("2022 format").Cells(start_line + 1, 1).Value = away_date
        Sheets("2022 format").Cells(start_line + 1, 2).Value = away_day
        Sheets("2022 format").Cells(start_line + 1, 3).Value = away_time
        Sheets("2022 format").Cells(start_line + 1, 5).Value = away_team
        Sheets("2022 format").Cells(start_line + 1, 6).Value = venue
        
           
        
        Next start_line
    
    
       
        
    End Sub

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    This may help, untested code:
    Sub formatdata()
        Dim start_line As Long
        Dim wsData As Worksheet
        Dim wsFormat As Worksheet
        Dim nr As Long
        
        Set wsData = Sheets("2022 data")
        Set wsFormat = Sheets("2022 format")
        wsFormat.Range("A2:M" & wsFormat.Range("A" & Rows.Count).End(xlUp).Row).ClearContents
        
        For start_line = 2 To wsData.Range("A" & Rows.Count).End(xlUp).Row
            With wsFormat
                nr = .Range("A" & Rows.Count).End(xlUp).Row + 1
                ' home
                .Cells(nr, 1).Value = wsData.Cells(start_line, 1).Value
                .Cells(nr, 2).Value = wsData.Cells(start_line, 2).Value
                .Cells(nr, 3).Value = wsData.Cells(start_line, 3).Value
                .Cells(nr, 5).Value = wsData.Cells(start_line, 4).Value
                .Cells(nr, 6).Value = wsData.Cells(start_line, 6).Value
                .Cells(nr, 7).Value = wsData.Cells(start_line, 7).Value
                ' away
                .Cells(nr + 1, 1).Value = wsData.Cells(start_line, 1).Value
                .Cells(nr + 1, 2).Value = wsData.Cells(start_line, 2).Value
                .Cells(nr + 1, 3).Value = wsData.Cells(start_line, 3).Value
                .Cells(nr + 1, 5).Value = wsData.Cells(start_line, 5).Value
                .Cells(nr + 1, 6).Value = wsData.Cells(start_line, 6).Value
                .Cells(nr + 1, 7).Value = wsData.Cells(start_line, 8).Value
            End With
        Next start_line
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

Posting Permissions

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