# 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 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 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

• 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 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.