Excel: Click Button for S takes to First Name Starting with S

[jmgaddis]

Have a spreadsheet with 3,000 names in a column... would like to have a button at the top of the spreadsheet for each letter... Button A would take you to first row of names starting with A, Button B to First B etc...

Any coding to get me started would be great.

Thanks in advance.
John

 

[SkipVought]

Hi,

Check ou the FIND method of a Range object. Make the Find string "A*".

Post back with your code and question.

Skip,

_[red][/red]

 

[SkipVought]

also...

use your maxro recorder.

Skip,

_[red][/red]

 

[jmgaddis]

Thanks Skip,

I have not started any code for this yet, wasn't sure where to beging, I will look into your suggestion when I return to work tomorrow.

Not real good with Excel VBA, but can usually muddle my way thru some of it.

Thanks again,
John

 

[jmgaddis]

Here is some code that I got to work and it moves to the cell I need it to....

Although it appears to be fast enough, possibly the code can be optimized... it currently finds the letter in question "anywhere in the field".. so for my Letter S, it may find it one thousand times before getting to the cell with the First Letter as an S.

Please let me know if you know how to make it find the First Letter of a Cell containing an S on the first search.

Thanks for any help.

John

Here is the code:


Private Sub Letter_S_Click()

With Worksheets(1).Range("K8:K3000")
strLetterToFind = "S"
Set c = .Find(strLetterToFind, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If Left(c, 1) = strLetterToFind Then
Cells.Range(c.Address).Select
Exit Sub
End If

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress

End If
End With

End Sub

 

[GlennUK]

Look at the worksheetfunction MATCH, with a wildcard S* for the search item.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jmgaddis]

Thanks Glenn,

Haven't had time to look into it any further today... will research your suggestion and post what I find out when I can.

Thanks,
John