Find text String and select cell address where it is found?
 

In this partial code below I first see what the text is in cell B23 and then
find the matching text in Row 1. I can find the text ok, and its address (in
$C$R value) in row 1, but I don't know how to use that info to select that
cell so that I can use an offset statement to paste values into a Range. I
guess I don't know how to convert the info I have into a Range() expression
so that I can select the cell.

Any solution will be much appreciated



Sub TrySearchMonth(month)

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If mySearch = userMonth Then
Address = mySearch.Address
End If

Debug.Print Address

End Sub

Find text String and select cell address where it is found?
 

you already have a range expression which holds a reference to the found
cell. It is the variable mySearch. Address is a property of a range, so
that is why you can do
msgbox mySearch.Address

Sub TrySearchMonth()

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If not mySearch is nothing Then
Address = mySearch.Address
mySearch.Select
Else
msgbox "Not found"
End If
End Sub

If mySearch is nothing, then userMonth was not found.

--
Regards,
Tom Ogilvy

"JCIrish" wrote in message
...
In this partial code below I first see what the text is in cell B23 and
then
find the matching text in Row 1. I can find the text ok, and its address
(in
$C$R value) in row 1, but I don't know how to use that info to select
that
cell so that I can use an offset statement to paste values into a Range. I
guess I don't know how to convert the info I have into a Range()
expression
so that I can select the cell.

Any solution will be much appreciated



Sub TrySearchMonth(month)

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If mySearch = userMonth Then
Address = mySearch.Address
End If

Debug.Print Address

End Sub

Find text String and select cell address where it is found?
 

Thanks for you help, Tom. I'll give that a try.
Regards

"Tom Ogilvy" wrote:

you already have a range expression which holds a reference to the found
cell. It is the variable mySearch. Address is a property of a range, so
that is why you can do
msgbox mySearch.Address

Sub TrySearchMonth()

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If not mySearch is nothing Then
Address = mySearch.Address
mySearch.Select
Else
msgbox "Not found"
End If
End Sub

If mySearch is nothing, then userMonth was not found.

--
Regards,
Tom Ogilvy

"JCIrish" wrote in message
...
In this partial code below I first see what the text is in cell B23 and
then
find the matching text in Row 1. I can find the text ok, and its address
(in
$C$R value) in row 1, but I don't know how to use that info to select
that
cell so that I can use an offset statement to paste values into a Range. I
guess I don't know how to convert the info I have into a Range()
expression
so that I can select the cell.

Any solution will be much appreciated



Sub TrySearchMonth(month)

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If mySearch = userMonth Then
Address = mySearch.Address
End If

Debug.Print Address

End Sub

Find text String and select cell address where it is found?
 

Tom,
Just wanted to let you know that your solution really helped me. I think my
problem was in not using the right form for my If..then??? At any rate ,
thanks again.


"Tom Ogilvy" wrote:

you already have a range expression which holds a reference to the found
cell. It is the variable mySearch. Address is a property of a range, so
that is why you can do
msgbox mySearch.Address

Sub TrySearchMonth()

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If not mySearch is nothing Then
Address = mySearch.Address
mySearch.Select
Else
msgbox "Not found"
End If
End Sub

If mySearch is nothing, then userMonth was not found.

--
Regards,
Tom Ogilvy

"JCIrish" wrote in message
...
In this partial code below I first see what the text is in cell B23 and
then
find the matching text in Row 1. I can find the text ok, and its address
(in
$C$R value) in row 1, but I don't know how to use that info to select
that
cell so that I can use an offset statement to paste values into a Range. I
guess I don't know how to convert the info I have into a Range()
expression
so that I can select the cell.

Any solution will be much appreciated



Sub TrySearchMonth(month)

Dim mySearch as Range

' TrySearchMonth Macro
' Macro recorded 4/14/2006 by John C. Flynn
'
Dim userMonth As Range

Set userMonth = Range("B23")


Set mySearch = Rows(1).Find(What:=userMonth.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print mySearch

If mySearch = userMonth Then
Address = mySearch.Address
End If

Debug.Print Address

End Sub

Find text String and select cell address where it is found?
 

Doesn't

Dim rng as Range
rng = Range(mySearch.Address)

work for you?

Find text String and select cell address where it is found?
 

actually, it would be
Dim rng as Range, mySearch as Range
set rng = Range(mySearch.Address)

but that seems like the long way around the block (use a range to get a
range) unless you are using the address of mySearch to use on a different
sheet and specify the same location.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Doesn't

Dim rng as Range
rng = Range(mySearch.Address)

work for you?