How do you "Find" on NonActive Page & Return Values?
Try this in a module. Change the D1:D5 range to suit your sheets range.
Sub ReturnThree()
Dim i As Integer
i = Range("A1").Value
Sheets("Sheet2").Activate
Range("D1:D5").Select
Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Copies over the last entry
ActiveCell.Resize(1, 3).Copy Sheets("Sheet1") _
..Range("A4")
'If you want to list the return value use this line
'ActiveCell.Resize(1, 3).Copy Sheets("Sheet1") _
..Range("A400").End(xlUp).Offset(1, 0)
Sheets("Sheet1").Activate
End Sub
HTH
Regards,
Howard
"addiction" wrote in message
nk.net...
I am a VBA newbie and desparately need to solve a problem for an Excel
spreadsheet I'm building for product sales. Can anyone help me with this
one?
On Sheet1 I want the User to type in the Product Number in Cell A1. This
will search down a random number of rows on Sheet2 in Column A until it
finds a "Match". The data contained within the 4 cells to the right of the
"Match" needs to be printed in Row 4 of Sheet1.
Example:
"Sheet1"
Product No. User Defined
2
Returned Data
3 116 524 727
"Sheet2"
Products Data
5 321 294 112 331
9 456 167 342 72
12 744 221 221 276
7 283 349 98 134
2 3 116 524 727
16 135 81 201 178
|