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