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
--
Gary''s Student - gsnu200906


"Forum Freak" wrote:

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

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 (MVP - Excel)


"Forum Freak" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Forum Freak" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Don't use this routine... there is a flaw in it.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Forum Freak" wrote in message
...
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

WOW!

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

Kenny


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Forum Freak" wrote in message
...
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

Hi

I could not get this to work :o(

Kenny


"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200906


"Forum Freak" wrote:

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

You did my corrected code, right?

--
Rick (MVP - Excel)


"Forum Freak" wrote in message
...
WOW!

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

Kenny


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Forum Freak" wrote in message
...
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

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 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx

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
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
"Rick Rothstein" wrote in message
...
You did my corrected code, right?

--
Rick (MVP - Excel)


"Forum Freak" wrote in message
...
WOW!

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

Kenny


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Forum Freak" wrote in message
...
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