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.
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
>.
>
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...
"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!
>.
>
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.