search and display a value in excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • squidge1990
    New Member
    • Nov 2006
    • 9

    #1

    search and display a value in excel

    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.
  • albertw
    Contributor
    • Oct 2006
    • 267

    #2
    Originally posted by squidge1990
    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

    Code:
    Private Sub cmdSearch_Click()
    For x = 0 To 2 'max number of search columns
    If Not Range("i2").Offset(0, x).Value = vbNullString Then
    SearchValue = Range("i2").Offset(0, x).Value
    Exit For
    End If
    Next x
    For y = 0 To 6 'max vertical range of column A
    If Range("a1").Offset(y, x).Value = SearchValue Then
    cRange = Range("a1").Offset(y, 0).Address & ":" & Range("a1").Offset(y, 3).Address
    Sheets(2).Range("a2:d2").Value = Sheets(1).Range(cRange).Value
    Sheets(2).Select
    Exit Sub
    End If
    Next y
    End Sub
    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

    Comment

    • squidge1990
      New Member
      • Nov 2006
      • 9

      #3
      thanks for your help it's worked just need a little tweaking to suit my program thanks again

      Comment

      Working...