find and copy to another sheet

[Paul]

Hi
I have a spreadsheet which has two sheets.
'Data' which has a list of parts and their costs week by week.
example: column A has title of 'Computer', column B has it's ref number
'9997' , column c has it's text ref 'PC'
then columns E to BC have the cost of running week by week '£0.87'.
There are approx 140 parts each are unique and are sorted by highest cost
each week, therefore the same part is not in the same row each week.
What i need to do is find a part by identifing the ''part/ref no/text ref''
then returning all of that particular row's info onto sheet 1.
I've tried using macros but they only work if the information is in the same
row each week.
Is there any functions or macros/vb code that will do what i need?
Thanks in advance
Paul

 

[Don Guillett]

Post your macro for comments and changes

 

[Paul Watkins]

Here's the macro which dosen't do what i need it to
Sheets("data").Select
Cells.Find(What:="computer", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
<<<<I Need to expand this part to search for the ref number and text
ref to ensure an exact macth>>>>>>>>>

Rows("55:55").Select
Selection.Copy
Sheets("sheet1").Select
Rows("27:27").Select
Selection.Insert Shift:=xlDown
<<<<< this just selects the same row every time instead i need it to copy
the entire row that the search finds>>>>>>>

Thanks

Paul Watkins

 

[Don Guillett]

try this (UN tested)

with Sheets("data")
myrow=cells.Find(What:="computer", After:=.cells(1,1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).row

.Rows(myrow).Copy Sheets("sheet1").cells(27,1)
end with

If all else fails, you may send your workbook to my address below along with
a snippet of this message and exactly what you want.

 

[Paul Watkins]

Thanks Don
This macro works to a point.
the find part needs to be expanded to search for 3 different columns that
all match in a row though.
e.g
computer/9777/pc
can this macro be expanded to search for all these at the same time?


i did use this vb code originally but could not get it to work to paste the
entire row though.

Dim vFind As Variant
Dim lFirstRow As String
Dim rFound As Range

vFind = Sheet1.Range("a11:a13").Value ****(A11 to A 13 contain the
search parameters)****
With Sheet2.Range("A:A") ****Sheet2 is called 'data'******
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("A13").Value =
..Cells(rFound.Row, "E")
Worksheets("Sheet1").Range("B13").Value =
..Cells(rFound.Row, "F") *****need to replace this to paste the entire row
instead*****
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"

Paul

 

[Don Guillett]

Without looking in too much detail since I can't see your layout, I would
v1=range("a11")
v2=range("a12")
v3=range("a13")
then find v1 and use if offset(0,2)=v2 and v3=offset(0,3) to determine the
next
etc.
I repeat my offer