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

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

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

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

Doesn't

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

work for you?

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?