can i select from list by typing first letter of word?

[Guest]

I HAVE VERY LONG LISTS OF VEHICLES/CUSTOMERS ETC, IS THERE ANY WAY THAT I CAN
KEY IN THE FIRST LETTER OF WHAT I WANT AND THE LIST MOVES DOWN TO THAT
SECTION?

 

[Otto Moehrbach]

Yes, but you don't need to yell (use of all caps is yelling).
You need a Worksheet_Change event macro and a regular macro to accomplish
this.
You can have a cell into which you type the first letter of the section you
want. Hit Enter and the screen will immediately jump to put the first cell
of that section at the top left corner of the screen.
The macros look like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "B1" Then Call ShiftList(Range("B1").Value)
End Sub

Sub ShiftList(sLetter As String)
Dim MyRng As Range
Dim SearchFor As String
Set MyRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
SearchFor = sLetter & "*"
On Error Resume Next
MyRng.Find(What:=SearchFor, After:=MyRng(MyRng.Count), _
LookAt:=xlWhole).Activate
If Err <> 0 Then
MsgBox "The letter '" & sLetter & "' cannot be found."
Err.Clear
Exit Sub
End If
On Error GoTo 0
With ActiveWindow
.ScrollRow = ActiveCell.Row
.ScrollColumn = 1
End With
End Sub

The first macro needs to be placed in a sheet module for the sheet that
holds all your data. The second macro goes into a regular module.
In the event that the letter you typed cannot be found, a message box will
pop up telling you so.
I assumed that B1 is the cell into which you enter the letter you want. I
also assumed your data is in Column A starting in A2 and going down.
If you send me a valid email address I will send you a small file that has
these macros placed in the proper modules.
My email address is (e-mail address removed). Remove "nop" from this address.
HTH Otto