a challenge!!! - 'keystroke jumping' in a list

[Henric Carabott]

Hey, does anyone know how to do this in excel:

In a long list of alphabetical data (eg: surnames), type a letter (eg: W)
and the cursor will jump to the area in list starting with that letter.

You know what I mean, some other ms apps (Outlook etc) do it when sorted on
a column - is there no other way in excel...? Have played around with
Smallscroll formulas but can't get it to pass a cell value instead of a
number.... any ideas out there???

Annaleigh.

[Jan Karel Pieterse]

Hi,

Not quite what you want, but who knows...

If you enable Data, filter, autofilter and click the
dropdown next to the Names columns first row, you get all
names listed. Pressing a letter gets you to the first name
that starts with it.

Regards,

Jan Karel Pieterse
Excel TA/MVP

>.
>

[David McRitchie]

Hi Anna (and Henric),

You could create a macro and invoke it with a keyboard shortcut
key combination. The macro would check the first letter of
each cell among the Text valued cells. Limiting to Text Valued
cells is built into Excel and save a lot of time.

You can modify the macro to start from where you are at, this
will start from the first cell of your selection. This is not going
to go fast because of the interface to enter the "W" as the character.
But if you have over 500 rows the macro might save time.

Sub FindFirstChar()
Dim Cell As Range, firstChar As String
firstChar = UCase(InputBox("Supply prefix character(s) " _
& "to find first occurence", "Find First Char(s)", "W"))
If firstChar = "" Then Exit Sub

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'in XL97
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
If Left(UCase(Cell), Len(firstChar)) = firstChar Then
Cell.Activate
GoTo leavemacro
End If
Next Cell
leavemacro:
Application.Calculation = xlCalculationAutomatic 'in XL97
Application.ScreenUpdating = True
End Sub

Information on coding consideration in the above macro can be found in
http://www.mvps.org/dmcritchie/excel/proper.htm

Information on installing a macro can be found at
Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

To create a shortcut key for a macro
Alt+F8 (Tools, macro, macros), select the macro, options, key in shortcut key.

To make sure you don't step on Excel's own shortcut keys check
Shortcut Keys in Excel 2000 (Excel Vers. 9)
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

BTW, what are smallscroll formulas? The closest I could come to
smallscroll was having to do with mouse wheel scrolling.
http://google.com/groups?th=507558b5d3cf4153

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Henric Carabott" <hcar...@bigpond.net.au> wrote...

[David McRitchie]

I kind of like Jan Karel's filtered list (on first char), is yours still
a viable alternative to his and to my macro where you have to
enter the letter. Could you shed a little more light on what
you mean.

"john" <jsee...@firthrixson.com> wrote ...
> Hi it took me ages to work this one out. I found the best
> way was to create a list box that looks at the data. Then
> in its properties you can link this to a cell. If you
> enter a letter in the cell it will select the nearest
> match in the list box. Good luck!

[Otto Moehrbach]

David
I like your idea but I would modify it this way. I would freeze the top
row so it would always be visible regardless of where the active cell is.
The user would type the letter he wants into, say, A1. An event macro would
trigger on that change and search for the first occurrence of that letter in
the first character and scroll the sheet to put that found cell at the top
left of the screen. I would appreciate your comment on this idea. HTH
Otto
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#916igNgCHA.3556@tkmsftngp08...

[Otto Moehrbach]

David
I like your idea but I would modify it this way. I would freeze the top
row so it would always be visible regardless of where the active cell is.
The user would type the letter he wants into, say, A1. An event macro would
trigger on that change and search for the first occurrence of that letter in
the first character and scroll the sheet to put that found cell at the top
left of the screen. I would appreciate your comment on this idea. HTH
Otto
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#916igNgCHA.3556@tkmsftngp08...

[john]

Hi it took me ages to work this one out. I found the best
way was to create a list box that looks at the data. Then
in its properties you can link this to a cell. If you
enter a letter in the cell it will select the nearest
match in the list box. Good luck!

>.
>

[David McRitchie]

Hi Otto and Anna,
How about freeze the first two rows then use an Event Maco
Getting interesting for the misc group, isn't it.
First row has column headers, second for entry.

The first is an even macro to install, right-click on the sheet tab,
view code, insert the code. More on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row <> 2 Then Exit Sub
If Trim(Target.Value) = "" Then Exit Sub
FindFirstChar Trim(Target.Value)
' Application.EnableEvents = False
' Target.Value = ""
' Application.EnableEvents = True
End Sub

Commented out code in the above to remove entered value,
since turning off EnableEvents is not worth the risk if anything
goes wrong.. Though some might consider it job security and
hope something goes wrong.

The following gets installed in the same workbook as a regular
macro. http://www.mvps.org/dmcritchie/excel/getstarted.htm
In this macro you can pass it an argument, if not there it
will obtain argument from row 2 of the column of the active cell
which leaves little chance of invoking the input box unless
that cell were empty. This macro could stand by itself.

Option Explicit
Sub FindFirstChar(Optional firstChar As String)
Dim Cell As Range
If firstChar = "" Then _
firstChar = Cells(2, ActiveCell.Column)
If firstChar = "" Then _

firstChar = UCase(InputBox("Supply prefix character(s) " _
& "to find first occurence", "Find First Char(s)", "W"))

If Intersect(Columns(ActiveCell.Column), Range("3:65536"), _
Selection.SpecialCells(xlConstants, xlTextValues)) _
Is Nothing Then Exit Sub

firstChar = UCase(Trim(firstChar))

If firstChar = "" Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'in XL97
'On Error Resume Next 'In case no cells in selection

For Each Cell In Intersect(Columns(ActiveCell.Column), _
Range("3:65536"), _

Selection.SpecialCells(xlConstants, xlTextValues))
If Left(UCase(Cell), Len(firstChar)) = firstChar Then
Cell.Activate
GoTo leavemacro
End If
Next Cell
leavemacro:
Application.Calculation = xlCalculationAutomatic 'in XL97
Application.ScreenUpdating = True
End Sub

Information on coding consideration in the above macro can be found in
http://www.mvps.org/dmcritchie/excel/proper.htm

HTH,

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Otto Moehrbach" <ot...@worldnet.att.net> wrote ...

> I like your idea but I would modify it this way. I would freeze the top
> row so it would always be visible regardless of where the active cell is.
> The user would type the letter he wants into, say, A1.

> "David McRitchie" <dmcri...@msn.com> wrote ...

> >
> > "Henric Carabott" <hcar...@bigpond.net.au> wrote...
> > > In a long list of alphabetical data (eg: surnames), type a letter (eg: W)
> > > and the cursor will jump to the area in list starting with that letter.
> > >

> > > Annaleigh.