Modify macro to find cells beginning with A - help needed

  • Thread starter Thread starter Forum Freak
  • Start date Start date
F

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
 
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
 
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.
 
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
 
WOW!

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

Kenny
 
Hi

I could not get this to work :o(

Kenny


Gary''s Student said:
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
Click to expand...
 
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
 
Back
Top