Alpha searching in lists.

P

Poltageist

How do i turn on the alpha search function in lists? Press the n key and you
jump to the first and siccessive entry that begins with N etc...?
 
P

Piranha

Hi,
This is not pressing a key, but close, and it works:

In this example you enter a letter in "B1" it will jump to the
location of the letter in Col "A"

Code is from Otto Moehrbach.

Put in worksheet module

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


Put in general module

Code
-------------------
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
 
J

Jim May

I was unable to get this going:
All attempts ended with Message box:

The letter 'F' could not be found. (just 1 example)

??????????
 
P

Piranha

Hi Jim,
I don't know how you are connecting to these Forums,
but if you are able to get attachments here is a sample
workbook where the code is working.
If you can't get it and still want it, you can e-mail me and
i will send it to you.
Its the same code for anyone else following this thread.
piranha488ATaolDOTcom
Change the AT and the obvious.
Dave


Jim said:
I was unable to get this going:
All attempts ended with Message box:

The letter 'F' could not be found. (just 1 example)

??????????


in
message news:p[email protected]...
Hi,
This is not pressing a key, but close, and it works:

In this example you enter a letter in "B1" it will jump to the
location of the letter in Col "A"

Code is from Otto Moehrbach.

Put in worksheet module

Code:
--------------------
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
--------------------


Put in general module

Code:
--------------------
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
Click to expand...


+-------------------------------------------------------------------+
|Filename: Macro - Find - Word in Column by Entering One Letter in Cell.zip|
|Download: http://www.excelforum.com/attachment.php?postid=3914 |
+-------------------------------------------------------------------+
Click to expand...
 
Top