VBA Find, Select, and Copy

[pinciak]

I have all my code but am stuck on this. I have all my points in column A in sheet named "Rates". I need to find the value "Price" and copy the 20 rows below it. Thank you

 

[Bairn90]

I've edited this from a recorded macro. Hopefully it should provide you with roughly what you need

Sub Macro1()
Cells.Find(What:="price", After:=[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate


rowcopy = ActiveCell.Row
Rows(rowcopy + 1 & ":" & rowcopy + 21).Copy
End Sub

 

[JLGWhiz]

I have all my code but am stuck on this. I have all my points in column A in sheet named "Rates". I need to find the value "Price" and copy the 20 rows below it. Thank you

Hi pinciak, Welcome to MrExcel Message Board and Forum.
If you could explain "points" and the relationship of that to "Price" it would be helpful in constructing the code to copy the twenty rows. It would also be helpful to know what you want to do with the data once copied. Maybe and example if you can work one up.

 

[pinciak]

I've edited this from a recorded macro. Hopefully it should provide you with roughly what you need

Sub Macro1()
Cells.Find(What:="price", After:=[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate


rowcopy = ActiveCell.Row
Rows(rowcopy + 1 & ":" & rowcopy + 21).Copy
End Sub

This work but it copies the whole row across I only need the values of the A column 20 rows down

 

[Bairn90]

This should do it.

Sub Macro1()
Cells.Find(What:="price", After:=[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate


rowcopy = ActiveCell.Row
Range("A" & rowcopy + 1 & ":A" & rowcopy + 21).Copy
End Sub

 

[JLGWhiz]

This work but it copies the whole row across I only need the values of the A column 20 rows down

This will locate "Price in Col A, then put any data in the 20 cells beneath that on the clipboard for pasting.

Dim c As Range
Set c = ActiveSheet.Range("A:A").Find("Price", LookIn:=xlValues)
If Not c Is Nothing Then
 C.Offset(1, 0).Resize(20, 1).Copy
End If