Issues finding date in range of data
 

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

Issues finding date in range of data
 

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

--
Doug


wrote in message
ups.com...
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

Issues finding date in range of data
 

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



On Oct 18, 9:51 pm, "Doug Glancy" wrote:
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

--
Doug

Issues finding date in range of data
 

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


wrote in message
ups.com...
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



On Oct 18, 9:51 pm, "Doug Glancy" wrote:
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

--
Doug