Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the earliest date from a range of cells | Excel Worksheet Functions | |||
finding a max date in a range in vlookup data | Excel Discussion (Misc queries) | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) | |||
finding a range of cells to match a date | Excel Programming |