Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find bug that's very strange
I have named a string variable strCurrentDate that concatenates data together
to form the following date format: dd/mm/yyyy. What it is then meant to do is go to a workbook and find that date in that format. So for example 01/06/2007. The macro is getting as far as activating the workbook and putting the date in the find function but is then coming back with: Run-time error '91' Object variable or with block variable not set What is happening here? Why can't it find the date that I'm looking for? My line of code is: Cells.Find(strCurrentDate).Activate That is all. When the macro pauses due to the bug I can hover over the strCurrentDate and it will show me the correct date (01/07/2007 for example) so there's nothing wrong with my variable. That is the exact same date format that I need to find in the workbook. When I stop the macro I can go in to the workbook and hit Ctrl+F and 01/07/2007 will already be in the find box. Hitting Find Next will find the correct cell. So why isn't the macro doing it for me. Tim Zych told me yesterday to try: Cells.Find (CDate(strCurrentDate)) I tried that code but it seems to change the format of the date to the American format and it doesn't find the cell. But it doesn't stop the macro with a bug either so maybe you're on the right lines. But when I go to the workbook and hit Ctrl+F the date in the find box is 7/1/2006 rather than the way I wanted it as 01/07/2007. So it prevents the bug but doesn't find the correct cell because the date format has been changed to the wrong format from the correct format. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find bug that's very strange
Try this sort of approach
stCurrentdate = DateSerial(2007, 10, 12) If stCurrentdate = "False" Then Exit Sub stCurrentdate = Format(stCurrentdate, "Short Date") On Error Resume Next Set cell = Cells.Find(What:=CDate(stCurrentdate), After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) On Error GoTo 0 If cell Is Nothing Then MsgBox "Date cannot be found" End If -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... I have named a string variable strCurrentDate that concatenates data together to form the following date format: dd/mm/yyyy. What it is then meant to do is go to a workbook and find that date in that format. So for example 01/06/2007. The macro is getting as far as activating the workbook and putting the date in the find function but is then coming back with: Run-time error '91' Object variable or with block variable not set What is happening here? Why can't it find the date that I'm looking for? My line of code is: Cells.Find(strCurrentDate).Activate That is all. When the macro pauses due to the bug I can hover over the strCurrentDate and it will show me the correct date (01/07/2007 for example) so there's nothing wrong with my variable. That is the exact same date format that I need to find in the workbook. When I stop the macro I can go in to the workbook and hit Ctrl+F and 01/07/2007 will already be in the find box. Hitting Find Next will find the correct cell. So why isn't the macro doing it for me. Tim Zych told me yesterday to try: Cells.Find (CDate(strCurrentDate)) I tried that code but it seems to change the format of the date to the American format and it doesn't find the cell. But it doesn't stop the macro with a bug either so maybe you're on the right lines. But when I go to the workbook and hit Ctrl+F the date in the find box is 7/1/2006 rather than the way I wanted it as 01/07/2007. So it prevents the bug but doesn't find the correct cell because the date format has been changed to the wrong format from the correct format. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find bug that's very strange
Hi Bob,
Does that not just assume that you might not be able to find the data? I may not have explained properly. Although the macro is not finding the date (01/07/2007) it is definitely in the workbook. As I said, it goes as far as populating the Find box with 01/07/2007 but then does not execute the search. But when I go and do it manually it finds it. "Bob Phillips" wrote: Try this sort of approach stCurrentdate = DateSerial(2007, 10, 12) If stCurrentdate = "False" Then Exit Sub stCurrentdate = Format(stCurrentdate, "Short Date") On Error Resume Next Set cell = Cells.Find(What:=CDate(stCurrentdate), After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) On Error GoTo 0 If cell Is Nothing Then MsgBox "Date cannot be found" End If -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... I have named a string variable strCurrentDate that concatenates data together to form the following date format: dd/mm/yyyy. What it is then meant to do is go to a workbook and find that date in that format. So for example 01/06/2007. The macro is getting as far as activating the workbook and putting the date in the find function but is then coming back with: Run-time error '91' Object variable or with block variable not set What is happening here? Why can't it find the date that I'm looking for? My line of code is: Cells.Find(strCurrentDate).Activate That is all. When the macro pauses due to the bug I can hover over the strCurrentDate and it will show me the correct date (01/07/2007 for example) so there's nothing wrong with my variable. That is the exact same date format that I need to find in the workbook. When I stop the macro I can go in to the workbook and hit Ctrl+F and 01/07/2007 will already be in the find box. Hitting Find Next will find the correct cell. So why isn't the macro doing it for me. Tim Zych told me yesterday to try: Cells.Find (CDate(strCurrentDate)) I tried that code but it seems to change the format of the date to the American format and it doesn't find the cell. But it doesn't stop the macro with a bug either so maybe you're on the right lines. But when I go to the workbook and hit Ctrl+F the date in the find box is 7/1/2006 rather than the way I wanted it as 01/07/2007. So it prevents the bug but doesn't find the correct cell because the date format has been changed to the wrong format from the correct format. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Find bug that's very strange
Well it does, but it also help you to find it better.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... Hi Bob, Does that not just assume that you might not be able to find the data? I may not have explained properly. Although the macro is not finding the date (01/07/2007) it is definitely in the workbook. As I said, it goes as far as populating the Find box with 01/07/2007 but then does not execute the search. But when I go and do it manually it finds it. "Bob Phillips" wrote: Try this sort of approach stCurrentdate = DateSerial(2007, 10, 12) If stCurrentdate = "False" Then Exit Sub stCurrentdate = Format(stCurrentdate, "Short Date") On Error Resume Next Set cell = Cells.Find(What:=CDate(stCurrentdate), After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) On Error GoTo 0 If cell Is Nothing Then MsgBox "Date cannot be found" End If -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... I have named a string variable strCurrentDate that concatenates data together to form the following date format: dd/mm/yyyy. What it is then meant to do is go to a workbook and find that date in that format. So for example 01/06/2007. The macro is getting as far as activating the workbook and putting the date in the find function but is then coming back with: Run-time error '91' Object variable or with block variable not set What is happening here? Why can't it find the date that I'm looking for? My line of code is: Cells.Find(strCurrentDate).Activate That is all. When the macro pauses due to the bug I can hover over the strCurrentDate and it will show me the correct date (01/07/2007 for example) so there's nothing wrong with my variable. That is the exact same date format that I need to find in the workbook. When I stop the macro I can go in to the workbook and hit Ctrl+F and 01/07/2007 will already be in the find box. Hitting Find Next will find the correct cell. So why isn't the macro doing it for me. Tim Zych told me yesterday to try: Cells.Find (CDate(strCurrentDate)) I tried that code but it seems to change the format of the date to the American format and it doesn't find the cell. But it doesn't stop the macro with a bug either so maybe you're on the right lines. But when I go to the workbook and hit Ctrl+F the date in the find box is 7/1/2006 rather than the way I wanted it as 01/07/2007. So it prevents the bug but doesn't find the correct cell because the date format has been changed to the wrong format from the correct format. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells.Find Bug that's very strange | Excel Programming | |||
find and replace macro strange behaviour | Excel Discussion (Misc queries) | |||
Strange AutoProtecting of cells | Excel Programming | |||
What is this Strange Characet? Find/Replace | Excel Programming | |||
Strange results using .FIND in Excel VBA | Excel Programming |