Pasting in a find box in a vba

[OleMiss2010]

That needs to do the following things:

1. Go to a Sheet

2. Select a cell from a set of cells

3. Copy the contents of that cell

4. Go to another Sheet

5. Find the first instance of the copied item on the current sheet

6. Offset 12 rows below where that item is

7. Copy that row

8. Go back to the original Sheet

9. Paste the row below the cell that was originally copied


Here is the code I have so far that is supposed to cover through step 8

[pre]

Sub CopyPaste()

i = 1
For Each Cell In Range("A2:A3")
Sheets("Sheet1").Select
i = i + 1
Range("A" & i).Select
Selection.Copy
Sheets("PEER DATA").Select
Cells.Find(What:=????????????, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
Application.CutCopyMode = False
ActiveCell.Offset(12, 0).Select
Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Sheet1").Select
Next Cell

End Sub

[/pre]

However, I cannot figure out a good way to perform my find function correctly, which is why I currently have ?s in that spot.


Any ideas?

 

[Luke M]

How's this?

[pre]

Sub CopyPaste()
Dim SearchValue As Variant

Application.ScreenUpdating = False
For Each C In Sheets("Sheet1").Range("A2:A3") 'I prefer to use C instead of Cell for clarity
SearchValue = C.Text
With Sheets("PEER DATA")
.Cells.Find(What:=SearchValue, After:=.Range("A1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(12, 0).EntireRow.Copy C.Offset(1, 0).EntireRow
End With
Next C
Application.ScreenUpdating = True
End Sub

[/pre]
One note...if you paste the found data 1 row below, won't that mess up the data in the next cell you were going to look at? E.g, macro looks at A2, returns data into row 3, but then it looks in A3.

 

[OleMiss2010]

That does the trick.


Yeah I know that will be a problem for searching for the next cell. Should not be a difficult fix. The range is actually going to be ("A2:A29") so I will have to put in some sort of offset for selecting the correct cell each time.

 

[OleMiss2010]

Luke M, how does this look as a solution? I actually only need the first 111 columns of the row 12 lines below my find. I am getting an error on my find section that is likely related to my columns and/or paste command. Any ideas on how I can reword?

[pre]

Sub CopyPaste()
Dim SearchValue As Variant

Application.ScreenUpdating = False
For Each C In Sheets("Sheet1").Range("A2:A29")
SearchValue = C.Text
With Sheets("PEER DATA")
.Cells.Find(What:=SearchValue, After:=.Range("A1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(12, 1).Columns("1:111").Copy C.Offset(0, 1).Paste
End With
Next C
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Luke M]

Check out the Resize method:

[pre]

Sub CopyPaste()
Dim SearchValue As Variant

Application.ScreenUpdating = False
For Each C In Sheets("Sheet1").Range("A2:A29")
SearchValue = C.Text
With Sheets("PEER DATA")
.Cells.Find(What:=SearchValue, After:=.Range("A1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(12, 1).EntireRow.Resize(1,111).Copy C.Offset(0, 1)
End With
Next C
Application.ScreenUpdating = True
End Sub

[/pre]
This tells macro to grab the whole row, and then change the selection to 1 row by 111 columns. Also, no need for the Paste method at end there. The Copy method is setup so that if you put a range after it, that range is the destination.

 

[OleMiss2010]

That did the trick. I've never used a Resize before. That is a useful tool. Thanks.