Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Excel 2002, WinXP
I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Sometimes converting the date to a long will work better:
TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The
Match with Date didn't work. The Match with CLng(Date) worked. Is there some rationale to this? My engineering brain needs a reason when something happens. Otto "Dave Peterson" wrote in message ... Sometimes converting the date to a long will work better: TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Not that I know.
One more option is to pick up the numberformat from one of the cells to be searched (if they have the same format) and use that. Otto Moehrbach wrote: Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The Match with Date didn't work. The Match with CLng(Date) worked. Is there some rationale to this? My engineering brain needs a reason when something happens. Otto "Dave Peterson" wrote in message ... Sometimes converting the date to a long will work better: TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Dave
You lost me there. What do you mean by "pick up the numberformat from one of the cells to be searched"? Thanks for taking the time to help me. Otto "Dave Peterson" wrote in message ... Not that I know. One more option is to pick up the numberformat from one of the cells to be searched (if they have the same format) and use that. Otto Moehrbach wrote: Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The Match with Date didn't work. The Match with CLng(Date) worked. Is there some rationale to this? My engineering brain needs a reason when something happens. Otto "Dave Peterson" wrote in message ... Sometimes converting the date to a long will work better: TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Dim myNumberFormat as string
dim FoundCell as range with .range("C:C") mynumberformat = .cells(1).numberformat set foundcell = .cells.find(what:=application.text(date,mynumberfo rmat), .... ..... Otto Moehrbach wrote: Dave You lost me there. What do you mean by "pick up the numberformat from one of the cells to be searched"? Thanks for taking the time to help me. Otto "Dave Peterson" wrote in message ... Not that I know. One more option is to pick up the numberformat from one of the cells to be searched (if they have the same format) and use that. Otto Moehrbach wrote: Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The Match with Date didn't work. The Match with CLng(Date) worked. Is there some rationale to this? My engineering brain needs a reason when something happens. Otto "Dave Peterson" wrote in message ... Sometimes converting the date to a long will work better: TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't find today's date
Thanks Dave. I'll file that one for future use. Otto
"Dave Peterson" wrote in message ... Dim myNumberFormat as string dim FoundCell as range with .range("C:C") mynumberformat = .cells(1).numberformat set foundcell = .cells.find(what:=application.text(date,mynumberfo rmat), .... .... Otto Moehrbach wrote: Dave You lost me there. What do you mean by "pick up the numberformat from one of the cells to be searched"? Thanks for taking the time to help me. Otto "Dave Peterson" wrote in message ... Not that I know. One more option is to pick up the numberformat from one of the cells to be searched (if they have the same format) and use that. Otto Moehrbach wrote: Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The Match with Date didn't work. The Match with CLng(Date) worked. Is there some rationale to this? My engineering brain needs a reason when something happens. Otto "Dave Peterson" wrote in message ... Sometimes converting the date to a long will work better: TheRng.find(what:=clng(date), ... Sometimes using application.match (with clng()) works better than .find, too. Otto Moehrbach wrote: Excel 2002, WinXP I'm down to the last hairs on this one. An OP has a list of March dates in Column C. He wants to find today's date and place the contents of cell A1 into the cell to the right of today's date. Simple enough. I write out a quick macro (below) and send it to him via the newsgroup. It doesn't work for him. The error is that the Find cannot find today's date. I tell him to send me his file. Sure enough, the macro does not work on his file. The only difference between his and my Column Cs is this: He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes) and dragged that formula down 30 some cells. I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some 30 cells. The macro works on my file but not on his. I entered the dates my way on his file and all is well. Why did the formula setup result in the error? Thanks for your help. Otto Sub FindDate() Dim TheRng As Range Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp)) TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value = Range("A1").Value End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
create button to find today's date in another tab | Excel Worksheet Functions | |||
Find Today's Date in a List-- How? | Excel Programming | |||
find cell with today's date | Excel Discussion (Misc queries) | |||
Find and display today's date cel | Excel Programming |