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.
search and display a value in excel
Collapse
X
-
Tags: None
-
hiOriginally posted by squidge1990im 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.
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
fill any searchvalue under the items of I-LCode: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
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
Comment