find location

[Jim G]

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

 

[paul.robinson]

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

 

[Jim G]

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 G]

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?

 

[Gary Keramidas]

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

 

[Jim G]

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)".

 

[Gary Keramidas]

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

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 G]

I've copied it verbatum and still get the same error.

I have the following data:

B1 = 31/07/2007 -this is the first month of the year and supplies data to
E13 to start the series.

B3 = 31/12/2007 - the date I'm looking to match

A13 to D13 are blank

E13 = 31/7/2007 - from B1

F13 = Blank

G13 = "Costs After" - column header repeated in Cols J,M,P,S,V,Y,AB,AE,AH,AK

H13="=DATE(YEAR(E13),MONTH(E13)+1+1,0)" - repeated in Cols
K,N,Q,T,W,Z,AC,AF,AI,AL

I'm testing this on sheet1 of the activebook so there is no other data.

 

[Jim G]

I Just hard coded T13 to text "31/12/2007" and the code worked. It didn't
work with formulas or normal formated dates. Changing them all to text would
defeat the purpose of having a control sheet that can be updated from one
cell each year.

Any solutions to this?

 

[paul.robinson]

Hi
Change
Curdate as String
to
Curdate as Variant

regards
Paul

 

[Jim G]

I'd tried that previously and it only works if it's a string.

However, I tried the code on a new workbook and it works it it's a string,
formula or date. So, would this indicate there is something in the workbook
that is causing the error.

 

[paul.robinson]

Funny - didn't work for me as a string but did as a Variant. Dates are
a pain, especially with Find and Filtering..
Paul

I'd tried that previously and it only works if it's a string.  

However, I tried the code on a new workbook and it works it it's a string,
formula or date.  So, would this indicate there is something in the workbook
that is causing the error.

- Show quoted text -

 

[Jim G]

Drives me nuts! It seems that when I open a new workbook the default format
for dates (entered as 31/12/2007 for example) will be "General" on sheet1 and
"dd/mm/yyyy" on sheet2. Hence my testing only worked if I started on the
correctly formated sheet!!!! Aaaggh!

I found a work around by linking the data report date to a cell in the
control sheet formatted as "General" to get the serial number. The following
code then converted the search area to serial and back again after the
location was found. I'm greatful for your help in pointing me in the right
direction.

Here it is if anyone is interested.

======================================
Sub FindCurMonth()

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

Application.ScreenUpdating = False

curDate = Sheets("Control").Range("B1").Value
Sheets("Cheops").Rows(13).NumberFormat = "general"

On Error Resume Next
Set CurLocation = Rows(13).Find(What:=curDate, LookIn:=xlValues)
Set rng = Range(CurLocation.Address)
Debug.Print (rng.Address)

Application.ScreenUpdating = True

If Not CurLocation Is Nothing Then
addr = CurLocation.Address
Application.Goto CurLocation.Offset(3, 0).Select
Else
MsgBox "Period Date Not Found"
End If

Sheets("Cheops").Rows(13).NumberFormat = "mmm-yy"

End Sub