I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). The following code returns a type mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")


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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub


--
Jim

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11*am, Jim G wrote:
I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). *The following code returns a type mismatch
error. *Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
* * Address = CurLocation.Address
* * CurLocation.Select
Else
* * MsgBox "Not found"
End If
End Sub

--
Jim

Thanks for the advice Paul,

I've made the following changes based on this, however, while the type match
error is catered for, the code still does not return the addres of the
current date.

If it matters, row 13 has nothing in the first 4 cols. The first month of
the year starts at Col E and then every third column for 12 months.
======================================
Sub FindDate2()

Dim CurLocation As Range
Dim curDate As Range
Dim rng As Range
Dim CurAddress As Range

Set curDate = Range("B3")
Debug.Print (curDate)
On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

If Not CurLocation Is Nothing Then
CurAddress = CurLocation.Address
CurLocation.Select


Else
MsgBox "Address Not found"
End If
End Sub


--
Jim


" wrote:

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11 am, Jim G wrote:
I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). The following code returns a type mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub

--
Jim

I just realised that the date I'm looking for is the following formula
"=DATE(YEAR(E13),MONTH(E13)+1+1,0)". E13 is the control date 31/7/07 being
the end of the first month of the year.

Would this be the reason the code can't find the date? Is there a way to
have the code check for the date value?
--
Jim


" wrote:

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11 am, Jim G wrote:
I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). The following code returns a type mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub

--
Jim

you can give this a try:

Sub FindCurMonth()

Dim CurLocation As Range
Dim addr As String
Dim curDate As String
Dim rng As Range

curDate = Range("B3").Value
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub


--


Gary


"Jim G" wrote in message
...
I just realised that the date I'm looking for is the following formula
"=DATE(YEAR(E13),MONTH(E13)+1+1,0)". E13 is the control date 31/7/07 being
the end of the first month of the year.

Would this be the reason the code can't find the date? Is there a way to
have the code check for the date value?
--
Jim


" wrote:

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11 am, Jim G wrote:
I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). The following code returns a type mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub

--
Jim

Hi gary,
I'm getting a 'run-time error 91' and "Object variable or With block
variable not set" at this point in the code, "Set rng =
Range(CurLocation.Address)".


--
Jim


"Gary Keramidas" wrote:

you can give this a try:

Sub FindCurMonth()

Dim CurLocation As Range
Dim addr As String
Dim curDate As String
Dim rng As Range

curDate = Range("B3").Value
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub


--


Gary


"Jim G" wrote in message
...
I just realised that the date I'm looking for is the following formula
"=DATE(YEAR(E13),MONTH(E13)+1+1,0)". E13 is the control date 31/7/07 being
the end of the first month of the year.

Would this be the reason the code can't find the date? Is there a way to
have the code check for the date value?
--
Jim


" wrote:

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11 am, Jim G wrote:
I'm trying to set the current selection to an address on a row that contain
dates equal to the end of each monht and matches the current date on a data
page (month ends muct match). The following code returns a type mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub

--
Jim

this works for me in excel 2003. i only have values in celsl a1, b3 and e13, so
i don't have all of your data.
did you paste the entire code block?


Option Explicit
Sub FindCurMonth()

Dim CurLocation As Range
Dim addr As String
Dim curDate As String
Dim rng As Range

curDate = Range("B3").Value
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub


--


Gary


"Jim G" wrote in message
...
Hi gary,
I'm getting a 'run-time error 91' and "Object variable or With block
variable not set" at this point in the code, "Set rng =
Range(CurLocation.Address)".


--
Jim


"Gary Keramidas" wrote:

you can give this a try:

Sub FindCurMonth()

Dim CurLocation As Range
Dim addr As String
Dim curDate As String
Dim rng As Range

curDate = Range("B3").Value
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub


--


Gary


"Jim G" wrote in message
...
I just realised that the date I'm looking for is the following formula
"=DATE(YEAR(E13),MONTH(E13)+1+1,0)". E13 is the control date 31/7/07 being
the end of the first month of the year.

Would this be the reason the code can't find the date? Is there a way to
have the code check for the date value?
--
Jim


" wrote:

Hi
Cells(1) in your Find is the first cell on the worksheet. Since you
are looking in row 13 the Find Method is getting upset. You can also
catch the error if the value is not found using On Error as below.

On Error Resume Next
Set CurLocation = ActiveSheet.Rows(13).Find(What:=curDate.Value, _
After:=Rows(13).Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)
On Error GoTo 0

Finally, you use Address as a variable name. Not a good idea, as it is
a VBA property of a Range - try CurAddress or something.

regards
Paul

On Feb 1, 5:11 am, Jim G wrote:
I'm trying to set the current selection to an address on a row that
contain
dates equal to the end of each monht and matches the current date on a
data
page (month ends muct match). The following code returns a type
mismatch
error. Can any one help

Sub FindCurMonth()

Dim CurLocation As Range

Dim curDate As Range

Set curDate = Range("B3")

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

Dim rng As Range
Set rng = Range(CurLocation.Address)
Debug.Print (rng)

If Not CurLocation Is Nothing Then
Address = CurLocation.Address
CurLocation.Select
Else
MsgBox "Not found"
End If
End Sub

--
Jim