Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick search required
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick search required
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" 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quick search required
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 "cashman" wrote in message oups.com: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with search | Excel Worksheet Functions | |||
Search multiple worksheets - Excel 97 | Excel Discussion (Misc queries) | |||
Edit macro to match entire cell contents | Excel Discussion (Misc queries) | |||
Wildcard search functions within Vlookup | Excel Worksheet Functions | |||
Excel Search Utility | Excel Discussion (Misc queries) |