By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,594 Members | 2,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,594 IT Pros & Developers. It's quick & easy.

search and display a value in excel

P: 9
im searching a page of products to find a certain product. the search must take the value that i have input into a certain cell then search through the like of products until in makes a match and then take details of that product and display it on a seperate page then move to the page that is displaying the details of the product. i have an idea of how to do this but as im kind of new to vba i dont know what sort of code to be using or how to search the range etc. if someone could help me out with this i would be very grateful.
Nov 23 '06 #1
Share this Question
Share on Google+
2 Replies


100+
P: 267
im searching a page of products to find a certain product. the search must take the value that i have input into a certain cell then search through the like of products until in makes a match and then take details of that product and display it on a seperate page then move to the page that is displaying the details of the product. i have an idea of how to do this but as im kind of new to vba i dont know what sort of code to be using or how to search the range etc. if someone could help me out with this i would be very grateful.
hi

create an example:
sheet 1
row 1, column A = Product ID
--------, col B = Year
--------, col C = Type
--------, col D = Number
fill the cells with whatever values you like (it's just an example)

copy sheet(1) row 1 col A-D to row 1 col I-L (search part)
copy sheet(1) row 1 to sheet(2) row 1 (so create same headers)

create a button (using VB options)
give it any name (e.g: cmdSearch)

now copy and paste following code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. For x = 0 To 2 'max number of search columns
  3. If Not Range("i2").Offset(0, x).Value = vbNullString Then
  4. SearchValue = Range("i2").Offset(0, x).Value
  5. Exit For
  6. End If
  7. Next x
  8. For y = 0 To 6 'max vertical range of column A
  9. If Range("a1").Offset(y, x).Value = SearchValue Then
  10. cRange = Range("a1").Offset(y, 0).Address & ":" & Range("a1").Offset(y, 3).Address
  11. Sheets(2).Range("a2:d2").Value = Sheets(1).Range(cRange).Value
  12. Sheets(2).Select
  13. Exit Sub
  14. End If
  15. Next y
  16. End Sub
  17.  
fill any searchvalue under the items of I-L
if you look for a product use cell I2, for a number use cell L2
then press the button Search

first the macro looks for the first cell in range i2:l2 which has any value
assigns the right column and will compare until found
then copy the values of the entire row to sheet(2) row 2
Nov 23 '06 #2

P: 9
thanks for your help it's worked just need a little tweaking to suit my program thanks again
Nov 23 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.