A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Quick search required



 
 
Thread Tools Display Modes
  #1  
Old September 10th, 2006, 11:36 AM posted to microsoft.public.excel.misc
cashman
external usenet poster
 
Posts: 1
Default 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  
Old September 10th, 2006, 02:28 PM posted to microsoft.public.excel.misc
Jim Cone
external usenet poster
 
Posts: 882
Default 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  
Old September 10th, 2006, 03:19 PM posted to microsoft.public.excel.misc
Jim May
external usenet poster
 
Posts: 64
Default 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  
Old October 15th, 2009, 05:01 PM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default

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:
Originally Posted by Jim May View Post
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.