|
|
How do you "Find" on NonActive Page & Return Values? |
|
Author |
Message |
lbfuentes
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
>>>
>>>
>>>
>
>
|
|
|
|
|
|
|