| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find date
Hi All,
I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0 I can't find out what is the cause of this error, because both the range I search in and the value I want to find are real Excel date values (numbers), only the display format is different. In other words: why does Excel find the numeric value of the date in the first range and why the display format string in the second string? -- Regards! Stefi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find date
Stefi,
The Find method uses the worksheet find dialog, which is set up to find the displayed value, not the underlying value. And, in case the formatting is ever changed, it is better to use code that reads the formatting prior to the Find: Range("first range").Find(Format(DateToFind, Range("first range").Cells(1,1).NumberFormat)).Row 0 HTH, Bernie MS Excel MVP "Stefi" wrote in message ... Hi All, I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0 I can't find out what is the cause of this error, because both the range I search in and the value I want to find are real Excel date values (numbers), only the display format is different. In other words: why does Excel find the numeric value of the date in the first range and why the display format string in the second string? -- Regards! Stefi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find date
Very tricky, thanks, Bernie!
-- Regards! Stefi €˛Bernie Deitrick€¯ ezt Ć*rta: Stefi, The Find method uses the worksheet find dialog, which is set up to find the displayed value, not the underlying value. And, in case the formatting is ever changed, it is better to use code that reads the formatting prior to the Find: Range("first range").Find(Format(DateToFind, Range("first range").Cells(1,1).NumberFormat)).Row 0 HTH, Bernie MS Excel MVP "Stefi" wrote in message ... Hi All, I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0 I can't find out what is the cause of this error, because both the range I search in and the value I want to find are real Excel date values (numbers), only the display format is different. In other words: why does Excel find the numeric value of the date in the first range and why the display format string in the second string? -- Regards! Stefi . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find date
I think she could have done it this way as well...
Range("first range").Find(What:=DateToFind, LookIn:=xlFormulas).Row 0 -- Rick (MVP - Excel) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Stefi, The Find method uses the worksheet find dialog, which is set up to find the displayed value, not the underlying value. And, in case the formatting is ever changed, it is better to use code that reads the formatting prior to the Find: Range("first range").Find(Format(DateToFind, Range("first range").Cells(1,1).NumberFormat)).Row 0 HTH, Bernie MS Excel MVP "Stefi" wrote in message ... Hi All, I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0 I can't find out what is the cause of this error, because both the range I search in and the value I want to find are real Excel date values (numbers), only the display format is different. In other words: why does Excel find the numeric value of the date in the first range and why the display format string in the second string? -- Regards! Stefi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find date
Hi Rick,
I tried your solution but it didn't work. To say the truth, I had to add Lookin:=xlValues to Bernie's solution to make it work safely. -- Regards! Stefi €˛Rick Rothstein€¯ ezt Ć*rta: I think she could have done it this way as well... Range("first range").Find(What:=DateToFind, LookIn:=xlFormulas).Row 0 -- Rick (MVP - Excel) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Stefi, The Find method uses the worksheet find dialog, which is set up to find the displayed value, not the underlying value. And, in case the formatting is ever changed, it is better to use code that reads the formatting prior to the Find: Range("first range").Find(Format(DateToFind, Range("first range").Cells(1,1).NumberFormat)).Row 0 HTH, Bernie MS Excel MVP "Stefi" wrote in message ... Hi All, I have two ranges of dates, both one's width is one column in which dates are created by =DATE() functions. In the first range cells are formatted like "yyyy.mm.dd", in the 2nd one like "mmmm d.". In a macro I have a date variable DateToFind created by a DateSerial() function. I used Range("first range").Find(What:=DateToFind ).Row 0 to check if DateToFind is included in first range and it worked. For the second range it gave a Type mismatch error, and i had to modify the Find line like this to make it work: Range("first range").Find(What:=Format(DateToFind , "mmmm d.")).Row 0 I can't find out what is the cause of this error, because both the range I search in and the value I want to find are real Excel date values (numbers), only the display format is different. In other words: why does Excel find the numeric value of the date in the first range and why the display format string in the second string? -- Regards! Stefi . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Julian date - find next highest date/number | Excel Worksheet Functions | |||
| Find the date of the coming up Saturday given the current date. | Excel Worksheet Functions | |||
| xls vba find method to find row that contains the current date | Excel Programming | |||
| Using variables to make a date and using find method to find that. | Excel Programming | |||
| Find date and copy range based on that date | Excel Programming | |||
Linear Mode
