Quick search required

[cashman]

I have a database of 5,500 surnames and associated data in excel [yes,
should probably use Access!]. It is always a task to quickly jump to
each entry using the "find" function - typing in the relative surname
will find the relative entry.

However, is there a way to have excel jump to just the change in in the
first letter of a group of surnames starting with the same alpha
character. I would envisage putting a control box or similar in the top
of the surname column [A1] . i.e just type in the letter "B" and the
cursor will jump to the first surname in column A containing the first
surname containg ithe letter B

In this instance, typing in the letter "B"in cell A1 would immediately
jump the cursor to cell
A4, typing in E in cell A1, would jump the cursor to A7 and so on
A
1 B
2 Aamic
3 Aauld
4 Baby
5 Cry
6 Deep
7 Ease
8 Foot

Thanks in advance
Cashman

 

[Jim Cone]

1. Following uses cell A1 as the data entry cell.
The list is assumed to be in column A
2. It works best if Freeze Panes is set for a row somewhere between
A1 and the top of your list.
3. It uses A5 as the first entry of your list.
Change A5 in this line to the actual cell...
"Set rngLookup = Me.Range("A5", Me.Cells(Me.Rows.Count, 1).End(xlUp)) "
4. You can enter one or more letters in A1.
5. If nothing found nothing happens.
6. The code goes in the sheet module not a regular/standard module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Jim Cone - San Francisco USA - September 2006
If Target.Address = "$A$1" Then
Dim rngLookup As Excel.Range
Dim strFind As String
Dim lngRow As Long
strFind = Target.Value & "*"
Set rngLookup = Me.Range("A5", Me.Cells(Me.Rows.Count, 1).End(xlUp))
On Error Resume Next
lngRow = Application.Match(strFind, rngLookup, 0) + rngLookup.Row - 1
If Err.Number <> 0 Then lngRow = Target.Row
On Error GoTo 0
Application.Goto Me.Cells(lngRow, 1), True
Target.Select
End If
End Sub
'------------


"cashman"
<[email protected]>
wrote in message
I have a database of 5,500 surnames and associated data in excel [yes,
should probably use Access!]. It is always a task to quickly jump to
each entry using the "find" function - typing in the relative surname
will find the relative entry.
However, is there a way to have excel jump to just the change in in the
first letter of a group of surnames starting with the same alpha
character. I would envisage putting a control box or similar in the top
of the surname column [A1] . i.e just type in the letter "B" and the
cursor will jump to the first surname in column A containing the first
surname containg ithe letter B
In this instance, typing in the letter "B"in cell A1 would immediately
jump the cursor to cell
A4, typing in E in cell A1, would jump the cursor to A7 and so on
A
1 B
2 Aamic
3 Aauld
4 Baby
5 Cry
6 Deep
7 Ease
8 Foot

Thanks in advance
Cashman

 

[Jim May]

Place a Command button at the top of your sheet (Using the Forms
control)
And assign the button to this Standard Module Macro.

So after you enter your letter of choice into A1, then click on the
button
The cursor should jump to the first instance of your A1 - letter
content;
HTH

Jim May

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/10/2006 by Jim May
'
Dim fchar As String
fchar = Range("A1") & "*"
'
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Find(What:=fchar,
After:=ActiveCell, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End Sub