VBA: incorrect date search

[silicon_beaver]

I did search previous posts but fail to find an answer.

Feb 01 2014, I was trying to obtain yahoo data, search in col(1) 360 days ago.
x = DateValue(Date - 360) ' note, date =2/1/2014, x=2/6/2013
Set Cell_find = Columns(1).Find(DateValue(x))

I need the day 2/6/2013. In stead, vba returns 12/6/2013 always. looks "12/6/2013" containing string "2/6/2013". But it's not correct result I expected.

I also tried.
x = Format(Date - 360, "mm/dd/yyyy") ' 12-mon ago
Columns(1).NumberFormat = "mm/dd/yyyy"

Set tt = Columns(1).Find(x) ' tried, but return "Nothing"
Set tt = Columns(1).Find(CDate(x)) ' tried, but still return "12/6/2013"
Set tt = Columns(1).Find(DateValue(x)) ' tried, return "12/6/2013" either

none correct.

Anyone knows how to fix it? Thanks in advance.

 

[JLGWhiz]

I did search previous posts but fail to find an answer.

Feb 01 2014, I was trying to obtain yahoo data, search in col(1) 360 days ago.
x = DateValue(Date - 360) ' note, date =2/1/2014, x=2/6/2013
Set Cell_find = Columns(1).Find(DateValue(x))

I need the day 2/6/2013. In stead, vba returns 12/6/2013 always. looks "12/6/2013" containing string "2/6/2013". But it's not correct result I expected.

I also tried.
x = Format(Date - 360, "mm/dd/yyyy") ' 12-mon ago
Columns(1).NumberFormat = "mm/dd/yyyy"

Set tt = Columns(1).Find(x) ' tried, but return "Nothing"
Set tt = Columns(1).Find(CDate(x)) ' tried, but still return "12/6/2013"
Set tt = Columns(1).Find(DateValue(x)) ' tried, return "12/6/2013" either

none correct.

Anyone knows how to fix it? Thanks in advance.

x = DateValue(Date - 360) ' note, date =2/1/2014, x=2/6/2013
Set Cell_find = Columns(1).Find(DateValue(x))

this code works for me.

 

[KrisSquare]

Try this:


Set Cell_find = Columns(1).Find(CDate(x), LookIn:=xlFormulas)

 

[silicon_beaver]

Try this:


Set Cell_find = Columns(1).Find(CDate(x), LookIn:=xlFormulas)

Thanks for your prompt reply.

I tried just now. But strangely, it still returns "12/6/2013"

 

[KrisSquare]

I used below code and Column 1 has these dates

[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/2/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/4/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/6/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/7/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/9/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2014[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2014[/TD]
[/TR]
[TR]
[TD="align: right"]12/6/2014[/TD]
[/TR]
</tbody>[/TABLE]

Sub dates()
Date1 = CDate("2/1/2015")
x = DateValue(Date1 - 360) ' note, date =2/1/2014, x=2/6/2013
Set Cell_find = Sheet4.Columns(1).Find(CDate(x), LookIn:=xlFormulas)
End Sub


It works fine. Is your date column formatted as date or text? Is it a value or Formula?

 

[silicon_beaver]

The yahoo data I am using is in format below

The date, 12/6/2013, (marked red) is what I obtained.
But date 2/6/2013 (marked green) is what I expected. I tried several ways. No luck.

x = DateValue(Date - 360)
Set Cell_find = Columns(1).Find(DateValue(x))

The code above returns 12/6/2013 so far. That's incorrect for me.

Date Open High Low Close Volume Adj Close
1/31/2014 177.01 179.29 176.92 178.18 193964600 178.18
1/30/2014 178.83 179.81 178.26 179.23 116122500 179.23
1/29/2014 177.58 178.55 176.88 177.35 215115500 177.35
1/28/2014 178.14 179.3 178.12 179.07 108689100 179.07
1/27/2014 179.06 179.52 177.12 178.01 179892000 178.01
1/24/2014 181.6 181.66 178.83 178.89 189641700 178.89
1/23/2014 183.37 183.4 181.82 182.79 132221300 182.79
1/22/2014 184.49 184.57 183.91 184.3 60639600 184.3
1/21/2014 184.7 184.77 183.05 184.18 88581900 184.18
1/17/2014 184.1 184.45 183.32 183.64 103899200 183.64
1/16/2014 184.28 184.66 183.83 184.42 72080000 184.42
1/15/2014 184.1 184.94 183.71 184.66 96591300 184.66
1/14/2014 182.29 183.77 181.95 183.67 104588800 183.67
1/13/2014 183.67 184.18 181.34 181.69 149436000 181.69
1/10/2014 183.95 184.22 183.01 184.14 101955600 184.14
1/9/2014 184.11 184.13 182.8 183.64 90529400 183.64
1/8/2014 183.45 183.83 182.89 183.52 96479300 183.52
1/7/2014 183.09 183.79 182.95 183.48 86018700 183.48
1/6/2014 183.49 183.56 182.08 182.36 106828500 182.36
1/3/2014 183.23 183.6 182.63 182.89 81330600 182.89
1/2/2014 183.98 184.07 182.48 182.92 119364600 182.92
12/31/2013 184.07 184.69 183.93 184.69 86119900 184.69
12/30/2013 183.87 184.02 183.58 183.82 56817500 183.82
12/27/2013 184.1 184.18 183.66 183.85 61814000 183.85
12/26/2013 183.34 183.96 183.32 183.86 63365000 183.86
, , , , , ,
12/9/2013 181.47 181.67 181.16 181.4 70124000 180.42
12/6/2013 180.67 181.11 180.15 180.94 127728000 179.96
12/5/2013 179.41 179.74 178.77 178.94 106934000 177.97
, , , , , ,
2/11/2013 151.74 151.9 151.39 151.77 73775000 148.77
2/8/2013 151.22 151.89 151.22 151.8 103133700 148.8
2/7/2013 151.21 151.35 149.86 150.96 162490000 147.97
2/6/2013 150.52 151.26 150.41 151.16 138762800 148.17
2/5/2013 150.35 151.48 150.29 151.05 113912400 148.06
2/4/2013 150.32 151.27 149.43 149.54 159073600 146.58
2/1/2013 150.65 151.42 150.39 151.24 131173000 148.25
1/31/2013 149.89 150.38 149.6 149.7 108975800 146.74

 

[JLGWhiz]

The problem is that it is finding the 2/6/2013 in 12/6/2013. Try this

x = DateValue(Date - 360) ' note, date =2/1/2014, x=2/6/2013
Set Cell_find = Columns(1).Find(DateValue(x), , xlValues, xlWhole)

 

[silicon_beaver]

JLGWhiz,

you are super!

your code is simple and fixes my error right. Thank you so mcuh!
(I had to develop a code to calculate Datediff, then if equal to 360. stop. It much slower than yours)

 

[JLGWhiz]

It is always better to post what you are working with so we can see it also. Speeds up resonse and saves a lot of guessing.
Glad it worked for you.
Regards, JLG