Modify macro to find cells beginning with A - help needed

[Forum Freak]

Hello

I have had no success Googling to find my answer so perhaps someone can help
me?

I recorded a macro involving "Find" and run it from a button. It works but I
want to modify it to only search column A and find the first word beginning
with the letter A.
From this I will be able to change the code to search for B etc. and assign
it to another button.

Any help would be appreciated.

Kenny W
Using XP Pro and Office 2003

 

[Gary''s Student]

Sub WheresTheA()
Dim r As Range
Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each rr In r
If Left(rr.Value, 1) = "a" Then
rr.Select
Exit Sub
End If
Next
End Sub

 

[Rick Rothstein]

Why have a button for each letter? Just use one button and ask the user
which column they want to search....

Sub FindInColumn()
Dim FindColumn As String, FoundCell As Range
FindColumn = Application.InputBox("What column?", _
"GetColumn Letter", Type:=2)
On Error Resume Next
Set FoundCell = Columns(FindColumn).Find(What:="a*", _
MatchCase:=False, After:=Cells(Rows.Count, _
FindColumn), SearchOrder:=xlColumns, _
LookAt:=xlPart, SearchDirection:=xlNext, _
LookIn:=xlValues)
If Not FoundCell Is Nothing Then
MsgBox "Cell " & FoundCell.Address & " starts with 'a' or 'A'."
Else
MsgBox "No cells in Column '" & FindColumn & "' start with 'a' or 'A'."
End If
End Sub

By the way, the user can specify the column either by its letter or number
designation.

 

[Rick Rothstein]

Don't use this routine... there is a flaw in it.

 

[Rick Rothstein]

This macro works correctly...

Sub FindInColumn()
Dim FindColumn As String, FoundCell As Range, FirstAddress As String
FindColumn = Application.InputBox("What column?", _
"GetColumn Letter", Type:=2)
On Error Resume Next
Set FoundCell = Columns(FindColumn).Find(What:="a*", _
MatchCase:=False, After:=Cells(Rows.Count, _
FindColumn), SearchOrder:=xlColumns, _
LookAt:=xlPart, SearchDirection:=xlNext, _
LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If InStr(1, FoundCell.Value, "a", vbTextCompare) > 1 Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = Columns(FindColumn).FindNext(FoundCell)
Loop While InStr(1, FoundCell.Value, "a", vbTextCompare) > 1 And _
FoundCell.Address <> FirstAddress
End If
End If
If Not FoundCell Is Nothing Then
MsgBox "Cell " & FoundCell.Address & " starts with 'a' or 'A'."
Else
MsgBox "No cells in Column '" & FindColumn & "' start with 'a' or 'A'."
End If
End Sub

 

[Forum Freak]

WOW!

That did it and far more. Rather than the message box I just want the cell
activating but many thanks anyway )

Kenny

 

[Forum Freak]

Hi

I could not get this to work (

Kenny

Sub WheresTheA()
Dim r As Range
Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each rr In r
If Left(rr.Value, 1) = "a" Then
rr.Select
Exit Sub
End If
Next
End Sub

 

[Rick Rothstein]

You did my corrected code, right?

 

[Forum Freak]

Hi Rick

I modified your code to do exactly what I want. Im sure its not the best way
but it works.

My spreadsheet is a bit like an address book list and I have the buttons
(yes 26) at the top
Each search column B and each button searches for a specific letter.
Whilst your message box method cuts down on coding I felt it quicker for the
user to just click a button.

Many thanks again, you have saved me a hell of a lot of scrolling down!!!

Kenny

Modified code below.

Code starts xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sub FindInColumnB()
Dim FindColumn As String, FoundCell As Range, FirstAddress As String
'FindColumn = Application.InputBox("What column?", _ ''''this row removed
"GetColumn Letter", Type:=2)

FindColumn = "B"
On Error Resume Next
Set FoundCell = Columns(FindColumn).Find(What:="B*", _
MatchCase:=False, After:=Cells(Rows.Count, _
FindColumn), SearchOrder:=xlColumns, _
LookAt:=xlPart, SearchDirection:=xlNext, _
LookIn:=xlValues)
If Not FoundCell Is Nothing Then
If InStr(1, FoundCell.Value, "B", vbTextCompare) > 1 Then
FirstAddress = FoundCell.Address
Do
Set FoundCell = Columns(FindColumn).FindNext(FoundCell)
Loop While InStr(1, FoundCell.Value, "B", vbTextCompare) > 1 And _
FoundCell.Address <> FirstAddress
End If
End If
If Not FoundCell Is Nothing Then

FoundCell.Activate

Else
MsgBox "No cells in Column '" & FindColumn & "' start with 'B'."
End If
End Sub

code ends
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx