Validation Data & Goto question

[ma]

Hi Everyone,

I tried this on my own, but got a big headache and no result.

I have an excel spread sheet with 9 columns and about 3000 rows of
just text. One of the columns has vendor names (column F) and their
products.
So, for one vendor, there may be one or 100 products listed in rows
until next vendor starts.
On top of this column I have a "validation listbox" with the list of
vendors.

If I click & drop down the list, I can pick any one of the vendor names.
Then I wanted to go to a row containing first occurrence of that vendor's
name.

Almost like one would be using Vlookup and GoTo, but for the life of me
I just don't know how to utilize these to accomplish what I need.

Is this possible, and if yes, how?

Thanks in advance.

Mike

 

[Jason Morin]

You're better off using AutoFilter which is what you're
appearing to emulate. Nevertheless, with your Validation
list in F1, try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim findrow As Long
Set rng = [F2:F4000]
If Not Intersect(Target, [F1]) Is Nothing Then
On Error GoTo NotFound:
findrow = Application.Match(Target.Value, rng, 0)
Application.Goto _
Reference:=rng(findrow), Scroll:=True
End If
Exit Sub
NotFound:
MsgBox "Not Found!"
End Sub

---
To use, right-click on the worksheet tab, go to View
Code, and paste in the code above. Press ALT+Q to return
to Excel.

HTH
Jason
Atlanta, GA

 

[ma]

Thanks, it worked great.

Mike

You're better off using AutoFilter which is what you're
appearing to emulate. Nevertheless, with your Validation
list in F1, try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim findrow As Long
Set rng = [F2:F4000]
If Not Intersect(Target, [F1]) Is Nothing Then
On Error GoTo NotFound:
findrow = Application.Match(Target.Value, rng, 0)
Application.Goto _
Reference:=rng(findrow), Scroll:=True
End If
Exit Sub
NotFound:
MsgBox "Not Found!"
End Sub

---
To use, right-click on the worksheet tab, go to View
Code, and paste in the code above. Press ALT+Q to return
to Excel.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi Everyone,

I tried this on my own, but got a big headache and no result.

I have an excel spread sheet with 9 columns and about 3000 rows of
just text. One of the columns has vendor names (column F) and their
products.
So, for one vendor, there may be one or 100 products listed in rows
until next vendor starts.
On top of this column I have a "validation listbox" with the list of
vendors.

If I click & drop down the list, I can pick any one of the vendor names.
Then I wanted to go to a row containing first occurrence of that vendor's
name.

Almost like one would be using Vlookup and GoTo, but for the life of me
I just don't know how to utilize these to accomplish what I need.

Is this possible, and if yes, how?

Thanks in advance.

Mike


.