Macro to find and copy to another sheet


P

Paul Watkins

Hello
I haven't posted here for a few years, but i'm stuck with this problem

I have a sheet that contains various parts and numbers along with prices
week by week.(Parts)
Column A has Part
Column B has number
Column C has base number
e.g: Part / 123/ 5c. then columns d onwards have a weekly price value in
them (52)

On Sheet 1 i enter three words and numbers (Part1 in cell A11, First Number
in cell A12 and Base Number in A13.
I then run this macro to search the 'Parts' sheet for an exact match for all
three and the return every weeks price for that part.
As you can see i would need to expand this code for 52 weeks (currently only
6 in the code)
I then need to duplicate this macro to find further 5 parts and return
those prices under the first one which starts at row 13, so my next macro
would target row 14 etc
Is there any way that when the match is found that i can return that entire
Row from the 'Parts' sheet onto sheet 1.therefor cutting down the code i
need.?
Thanks in advance

Paul

I'm using this VB code to do this


Sub findcopy1()
Dim vFind As Variant
Dim lFirstRow As String
Dim rFound As Range

vFind = Sheet1.Range("a11:a13").Value

With Sheet2.Range("A:A")
Set rFound = .Find(vFind(1, 1), LookIn:=xlValues)
If Not rFound Is Nothing Then
lFirstRow = rFound.Row
Do
If rFound.Offset(, 1).Value = vFind(2, 1) And
rFound.Offset(, 2).Value = vFind(3, 1) Then
Worksheets("Sheet1").Range("J13").Value =
..Cells(rFound.Row, "E")
Worksheets("Sheet1").Range("K13").Value =
..Cells(rFound.Row, "F")
Worksheets("Sheet1").Range("L13").Value =
..Cells(rFound.Row, "G")
Worksheets("Sheet1").Range("M13").Value =
..Cells(rFound.Row, "H")
Worksheets("Sheet1").Range("N13").Value =
..Cells(rFound.Row, "I")
Worksheets("Sheet1").Range("O13").Value =
..Cells(rFound.Row, "E")
Exit Sub
End If
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Row > lFirstRow
End If
End With

MsgBox "No Data Found"
End Sub
 
Ad

Advertisements

P

Paul Watkins

Forgive my question, i'm quite rusty with code, where does that go in the
macro

Thanks
Paul
 
P

Paul Watkins

I placed the code here but it didn't work (application defined or object
defined error)

Worksheets("Sheet1").Range("A1").Value = .Cells(rFound.EntireRow)
Can anyone suggest or help with the code?
 
Ad

Advertisements

P

Paul Watkins

Anyone?

Paul Watkins said:
I placed the code here but it didn't work (application defined or object
defined error)

Worksheets("Sheet1").Range("A1").Value = .Cells(rFound.EntireRow)
Can anyone suggest or help with the code?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top