Macro to find and copy to another sheet
 

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

Macro to find and copy to another sheet
 

rFound.entirerow

"Paul Watkins" wrote:

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

Macro to find and copy to another sheet
 

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

Thanks
Paul
"Joel" wrote in message
...
rFound.entirerow

"Paul Watkins" wrote:

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

Macro to find and copy to another sheet
 

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?



"Paul Watkins" wrote in message
...
Forgive my question, i'm quite rusty with code, where does that go in the
macro

Thanks
Paul
"Joel" wrote in message
...
rFound.entirerow

"Paul Watkins" wrote:

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

Macro to find and copy to another sheet
 

Anyone?

"Paul Watkins" wrote in message
...
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?



"Paul Watkins" wrote in message
...
Forgive my question, i'm quite rusty with code, where does that go in the
macro

Thanks
Paul
"Joel" wrote in message
...
rFound.entirerow

"Paul Watkins" wrote:

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