Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Validation Data & Goto question
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
Thanks, it worked great.
Mike "Jason Morin" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
Data Validation list selection question | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |