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" <(E-Mail Removed)> wrote in message
news:172E2F8B-04D9-4FA9-A1F1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:60462D79-0885-4C01-82BA-(E-Mail Removed)...
>> >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.
>>
>>
>>
|
Similar Threads
