If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
Hi
I have a colleague who is looking to do something similar to this original posting. We have tried both of the suggested solutions but keep getting error messages. We are using Excel 2007 with a macro enabled workbook. Can someone suggest how this can be done in 2007? Thanks Shirley Quote:
|
Thread Tools | |
Display Modes | |
|
|