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
-
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
9
