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





PostPosted: Tue Nov 08 18:08:54 CST 2005 Top

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

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

Excel129  
 
 
Rowan





PostPosted: Tue Nov 08 18:08:54 CST 2005 Top

Excel Programming >> How do you "Find" on NonActive Page & Return Values? 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


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





PostPosted: Tue Nov 08 18:46:57 CST 2005 Top

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



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


 
 
addiction





PostPosted: Tue Nov 08 22:23:59 CST 2005 Top

Excel Programming >> How do you "Find" on NonActive Page & Return Values? Thank you so much. This worked. And the resource link you gave me is
incredibly useful.

~****ion




> 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.hide-link.com/
>
> Hope this helps
> Rowan
>

>> I am a VBA newbie and desperately 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





PostPosted: Tue Nov 08 22:43:40 CST 2005 Top

Excel Programming >> How do you "Find" on NonActive Page & Return Values? You're welcome.


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


>
>>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.hide-link.com/
>>
>>Hope this helps
>>Rowan
>>

>>
>>>I am a VBA newbie and desperately 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
>>>
>>>
>>>
>
>