Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.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 |
#2
Posted to microsoft.public.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 addiction wrote: 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 |
#3
Posted to microsoft.public.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 "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 |
#4
Posted to microsoft.public.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. ~Addiction "Rowan Drummond" wrote in message ... 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 addiction wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you "Find" on NonActive Page & Return Values?
You're welcome.
addiction wrote: Thank you so much. This worked. And the resource link you gave me is incredibly useful. ~Addiction "Rowan Drummond" wrote in message ... 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 addiction wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can "countifs" be utilized to return a count of unique values? | Excel Worksheet Functions | |||
Can "COUNTIFS" be utilized to return unique values? | Excel Worksheet Functions | |||
FIND function, when FALSE how can you return nothing (" ") instead of#VALUE or #NAME | Excel Discussion (Misc queries) | |||
keyboard shortcut to return to previous cell after "find" or "got. | New Users to Excel | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |