I am not following all of what you are doing or your rules, but this should
get you started:

Sub ABC()
Set sh1 = Worksheets("Item History")
Set sh2 = Worksheets("Current Data")
sh1.AutoFilterMode = False

With sh1
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End(xlUp)) _
.Resize(, 12)
End With
ans = InputBox("Enter Item Number")
If ans = "" Then Exit Sub
If Application.Count(rng.Columns(2), ans) = 0 Then
MsgBox "Not found"
Exit Sub
End If
rng.AutoFilter Field:=2, Criteria1:=ans
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 12)
Set rng2 = rng1.Columns(12).SpecialCells(xlVisible)
For Each cell In rng2
cell.EntireRow.Select
MsgBox cell.Offset(0, -2).Value & _
" has a Preference rating of " & cell.Value
Next



End Sub

--
Regards,
Tom Ogilvy


"QuickLearner" wrote:


My earlier post was obviously too wordy or difficult.
Perhaps an example of what I am trying to achieve may help.

Have a look at the attached workbook. It has two worksheets:
Item history, which has an items history and the priority of quotes
that the current information takes preference on.
Current Data is the worksheet I would like to achieve by filtering,
looping through cells???? This row represents the most prefered data
for a particular item.

Ideally, by selecting this item the user will be presented with a
variety of options for viewing the items information. (filter by
supplier, or by quotes etc)

Any ideas of how I can achieve this?


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4896 |
+-------------------------------------------------------------------+

--
QuickLearner
------------------------------------------------------------------------
QuickLearner's Profile: http://www.excelforum.com/member.php...o&userid=35483
View this thread: http://www.excelforum.com/showthread...hreadid=552634