Issues finding date in range of data

[Guest]

Hello,

I have read a number of posts, on this subject but the answer has been
elusive.

Essentially my worksheet gets reused each year, so to find the correct
first day of the year I set a variable to cell B6 in the first line.
This cell does hold a date value. On the sheet containing the column
of dates, I set the first date in the column to be = the date in B6.
So essentially they should hold the exact same date value, the only
difference is how I formatted the date to show.

However, the code does not find the date! Any insights would be
appreciated.

Greg


FindDate = ActiveSheet.Range("B6")
Worksheets("Seg Proj").Activate
Set FoundCell = Columns("A:A").Find(What:=(FindDate),
LookIn:=xlFormulas)

'check to see if found for debug purposes
If FoundCell Is Nothing Then
MsgBox "wasn't found"
Else
MsgBox FoundCell.Row
End If

FoundCell.Activate

 

[Doug Glancy]

Greg,

I'm actually not sure why this works (at least for me) but declaring
FindDate as a double seems to do it:

Sub test()

Dim finddate As Double
Dim foundcell As Range

FindDate = ActiveSheet.Range("B6")
Worksheets("Seg Proj").Activate
Set foundcell = Columns("A:A").Find(What:=(FindDate),
LookIn:=xlFormulas)

'check to see if found for debug purposes
If foundcell Is Nothing Then
Debug.Print "wasn't found"
Else
Debug.Print foundcell.Row
End If

FoundCell.Activate


End Sub

 

[Guest]

Thanks Doug,

I had it declared as a 'date', but changing it to 'double' does not
change anything on my end.

Not sure if it matters, but my users enter the current year and the
first day of January is defined as =date(cell with year,1,1)

I am trying to copy all the data of the individual sheets into a master
data table. I could hard code the position of the dates, except leap
year would throw everything off, so I figured searching down for the
first of each month to find the position would be the most efficient.
The first date on my data table just refers to the cell with the
formula mentioned above, so the should be exactly the same, I guess it
is just finding the right search parameters to match them up.

Any other ideas?

Greg

 

[Doug Glancy]

Greg,

Actually, now when I change it to as you describe, your original code works,
dimming as double doesn't. So I'm stumped.

Doug

Thanks Doug,

I had it declared as a 'date', but changing it to 'double' does not
change anything on my end.

Not sure if it matters, but my users enter the current year and the
first day of January is defined as =date(cell with year,1,1)

I am trying to copy all the data of the individual sheets into a master
data table. I could hard code the position of the dates, except leap
year would throw everything off, so I figured searching down for the
first of each month to find the position would be the most efficient.
The first date on my data table just refers to the cell with the
formula mentioned above, so the should be exactly the same, I guess it
is just finding the right search parameters to match them up.

Any other ideas?

Greg

 

[Guest]

I work with a guy who does office programing and he helped me out. The
solution is simple and has to do with formating. Turns out the find
command does not reach in and see the date as the base date number that
excell uses (depsite formatting). Find essentially sees the formated
string (01/01/07 versus 01-Jan).

- The key was to Dim my variable as a string
- Assign a value to my variable (cell with date)
- Then to format the variable to have the same format as the
sheet.range I am searching through. This is what trips people. I
suppose I could have just formatted the search through dates to have
the same format, but it is good to know how this feature works.
- Search with Find command and look in values versus formulas.

See working code below.

Cheers, Greg


Dim FindDate As String
Dim Month As String
Dim FoundCell As Range
Dim Year As Integer


Worksheets("1").Activate
Month = ActiveSheet.Name

FindDate = ActiveSheet.Range("B6").Value

FindDate = Format(FindDate, "dd-mmm")

Worksheets("Seg Proj").Activate
Set FoundCell = Columns("A:A").Find(What:=(FindDate),
LookIn:=xlValues)


'check to see if found for debug purposes
If FoundCell Is Nothing Then
MsgBox "wasn't found"
Else
MsgBox FoundCell.Row
FoundCell.Activate
End If

 

[Doug Glancy]

Thanks for the explanation.

Doug