How do you "Find" on NonActive Page & Return Values?

[addiction]

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

 

[Rowan Drummond]

Why not just use Vlookup formulas in row 4 of sheet1 eg:
=VLOOKUP($A$1,Sheet2!$A:$D,2,0)

For more on vlookup see:
http://www.contextures.com/xlFunctions02.html

Hope this helps
Rowan

 

[L. Howard Kittle]

Try this in a module. Change the D15 range to suit your sheets range.

Sub ReturnThree()
Dim i As Integer
i = Range("A1").Value
Sheets("Sheet2").Activate
Range("D15").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]

Thank you so much. This worked. And the resource link you gave me is
incredibly useful.

~Addiction

 

[Rowan Drummond]

You're welcome.

Thank you so much. This worked. And the resource link you gave me is
incredibly useful.

~Addiction