Find cells based on values in different cell, copy and paste adjacent cells

  • Referring to the attached file, I am trying to write code that will find the first three cells in column C based on the value of cell H4 in the sheet Boys8. Then I want to copy the contents of the corresponding cells in columns A, B, C and Cell A2 to the corresponding columns in sheet TeamQualifiers. For example copy these cells from rows 6, 10, 12 and A2:
    ANDREW L agn St. Agnes Grade 8 Boys
    ANTHONY J agn St. Agnes Grade 8 Boys
    MATTHEW B agn St. Agnes Grade 8 Boys


    and then paste them into sheet TeamQualifiers in the corresponding columns.
    Then I need to repeat the process using the value in cell H5 (St. Gerald) and paste the similar data starting with the first blank row in TeamQualifiers. I'm relatively new to VBA and would appreciate any help.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Find cells based on values in different cell, copy,paste adjacent cells-SOLVED


    I have found the solution to my problem. Thanks to all who viewed it.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Find cells based on values in different cell, copy and paste adjacent cells


    I had to modify my initial intent to find team runners if they finish outside the top 22 runners and then copy them into the RegionQualifiers sheet. This is the code.


    [VB]
    'Finds all three team runners if they finish outside of top 22 runners.
    bottomC = Range("c" & Rows.count).End(xlUp).Row
    bottomH = Range("h" & Rows.count).End(xlUp).Row

    For r = 4 To bottomH
    If Range("G" & r) = "1" Then FirstTeam = Trim(Range("H" & r))
    If Range("G" & r) = "2" Then SecondTeam = Trim(Range("H" & r))
    Next r

    Dim Counter As Integer
    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r

    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = SecondTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = SecondTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r

    'Copies Race Name into column D in RegionQualifiers.
    Dim myDest As Range
    Sheets("RegionQualifiers").Range("D" & Rows.count).End(xlUp)(2).Value = Range("a2").Value
    With Sheets("RegionQualifiers")
    Set myDest = .Range("a" & Rows.count).End(xlUp).Offset(, 3)
    With .Range("d" & Rows.count).End(xlUp)
    .AutoFill Sheets("RegionQualifiers").Range(.Cells, myDest), xlFillSeries
    End With
    End With
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.