Find record starting with ... macro

[kandinsky]

Hey peop's

I am making a "template" for the office, where people can just copy al
their customer names into, and then use some standedized macros to wor
with the records.

In the sheet, I have the alphabet in the top, and I would like t
assign macros to each letter, so the sheet jumps to the first cell i
colomn A, that have a name which starts with a fx a "C" if you clic
the letter "C" in the top of the sheet.

It all seems possible, but I can't figure out how to search the firs
letter in the cells when i run through the search range...

Any one out there with a quick fix?

Thx,
Jørge

 

[Kevin Beckham]

Sub LocateFirstLetter()

'set range to search

End Sub

 

[Tom Ogilvy]

Dim rng as Range
Dim fndCell as Range
'find the range of names. Assume they start in A5
set rng = Range(Cells(1,5),cells(rows.count,1).End(xlup))

set fndCell = rng.Find(What:="C*", _
After:=rng(rng.count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if not fndCell is nothing then
fndCell.Select
else
msgbox "no names starting with C were found"
End if


Obviously you could generalize the code to look for the letter in the
activecell.

 

[Kevin Beckham]

Oops, sorry about the last
The code is not elegant, but

Function SearchFirstLetter(ByVal searchLetter As String)
As Range
Dim c As Range
Dim rngSearch As Range

'set search range
Set rngSearch = Range(Range("A2"), Range("A2").End
(xlDown))

searchLetter = LCase(searchLetter)

For Each c In rngSearch
If LCase(Left(c.Value, 1)) = searchLetter Then
Set SearchFirstLetter = c
Exit Function
Next c
Set SearchFirstLetter = Nothing
End Function

Kevin Beckham

 

[kandinsky]

[Edit