Using find with dates in VBA

  • Thread starter Thread starter Hardy
  • Start date Start date
H

Hardy

Hi,

Gonig mad with something probably quite simple.

Using code below to check for date value in range

Sub CheckSwapsDate()

Dim SwapsDataCheck As Range

Workbooks("Book1").Activate
Worksheets("Links").Activate

With Worksheets("Links").Range("A28:A48")
Set SwapsDataCheck = .Find("24/03/2004", LookIn:=xlValues)
If SwapsDataCheck Is Nothing Then
MsgBox "Data not found"
QuitFlag = True
End If
End With

End Sub

In Links worksheet, dates appear as dd mmm yyyy format and in cell (i
formula edit box) as xx/mm/yyyy format. The are not text in th
worksheet. If, in the worksheet, I use Edit Find and put in 24/03/200
it finds that date in the list. Using this alone, or within quotes i
above code does not work, or with date serial number. Code works fine
as if I change one date in list to 12 and find 12 using above code, th
MsgBox does not show.

Any thoughts appreciated,

Thanks
 
Hi
try changing the line
Set SwapsDataCheck = .Find("24/03/2004", LookIn:=xlValues)

to
Set SwapsDataCheck = .Find(DateSerial(2004,3,24), LookIn:=xlValues)
 
Assuming your dates are constants and not produced by formulas:

Sub CheckSwapsDate()

Dim SwapsDataCheck As Range
Dim dt as Date

Workbooks("Book1").Activate
Worksheets("Links").Activate
dt = cDate("24/03/2004")
With Worksheets("Links").Range("A28:A48")
Set SwapsDataCheck = .Find(dt, LookIn:=xlFormulas)
If SwapsDataCheck Is Nothing Then
MsgBox "Data not found"
QuitFlag = True
End If
End With

End Sub


or try
Set SwapsDataCheck = .Find(clng(dt), LookIn:=xlFormulas)
 
Hi,

Thanks, but still no success. The search does not work on date seria
numbers, similar to if you do it in worksheet with Edit Find.

Tom, set up your approach, but it was still not having it.

I have dropped into attached spreadsheet that contains code below an
nothing more (apart from dates in Sheet1).

Sub CheckSwapsDate()

Dim SwapsDataCheck As Range
Dim SwapsDate As Date

Workbooks("aargh.xls").Activate
Worksheets("Sheet1").Activate
SwapsDate = CDate("24/03/2004")
With Worksheets("Sheet1").Range("A1:A21")
Set SwapsDataCheck = .Find(SwapsDate)
If SwapsDataCheck Is Nothing Then
MsgBox "Data not found"
QuitFlag = True
End If
End With

End Su

Attachment filename: aargh.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49474
 
I think the problem is that you are doing nothing after you have foun
the cell

try something like .find(swapsdate).activate

HTH

Dav
 
That does not work for me. If I put number in date range (e.g. 12) an
change .Find(SwapsDate) to .Find(12) it works and goes to MsgBox. Jus
the dates that are the proble
 
Hi
tried both Tom's and mine solution and both work. Are you sure your
dates are really entered as dates and not as text?
 
I moved you code to a general module and it worked for me. I also modified
it slightly and this worked as well:

Sub CheckSwapsDate()

Dim SwapsDataCheck As Range
Dim SwapsDate As Date

'Workbooks("aargh.xls").Activate
Worksheets("Sheet1").Activate
SwapsDate = CDate("24/03/2004")
With Worksheets("Sheet1").Range("A1:A21")
Set SwapsDataCheck = .Find(SwapsDate, LookIn:=xlFormulas)
If SwapsDataCheck Is Nothing Then
MsgBox "Data not found"
QuitFlag = True
Else
SwapsDataCheck.Activate
End If
End With

End Sub
 
Yes

but the date you are looking for is in the date range you supplied.
So when you run the macor the after you find the date
you go to the If Statement it looks for a non result
if the result is false then the messagebox will show
else End SUb is reached and nothing happens

Dav
 
Tom - Sorted. I moved to module and copied revised code and works fine
Not quite sure where I was going wrong. Thanks v. much
 
Back
Top