excel97 vs excel2000 (Find instruction)

[cyrille]

Hi,
I have the following procedure:

1 With Worksheets(1).Range("b10:b5000")
2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
3 If Not C Is Nothing Then
4 firstAddress = C.Address
5 C.Activate
6 End If
7 End With

It is perfectly working under Excel2000 but not under Excel97. In the
latter case, it stops in line 2 (set C =...) and I obtained the
following error : (1004)
Impossible to read the find property of the range class
(My translation of the error message is probably terrible!).
Can you help me to write this in a different manner ? The funny thing,
this code is given as an example of the Find property in the excel97
help file...
Thanks in advance.
Cyrille

[JE McGimpsey]

That code will work in XL97. However, you're probably running it from a
button, right? If so, you need to set the button's Take Focus On Click
property to False.

This was fixed in XL00.

In article <422059fa$0$30319$626a14ce@news.free.fr>,
cyrille <patchaltair@yahoo.fr> wrote:

> Hi,
> I have the following procedure:
>
> 1 With Worksheets(1).Range("b10:b5000")
> 2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
> 3 If Not C Is Nothing Then
> 4 firstAddress = C.Address
> 5 C.Activate
> 6 End If
> 7 End With
>
> It is perfectly working under Excel2000 but not under Excel97. In the
> latter case, it stops in line 2 (set C =...) and I obtained the
> following error : (1004)
> Impossible to read the find property of the range class
> (My translation of the error message is probably terrible!).
> Can you help me to write this in a different manner ? The funny thing,
> this code is given as an example of the Find property in the excel97
> help file...
> Thanks in advance.

[cyrille]

Correct, I was running this function from a button. You're fantastic,
thanks so much !!!

JE McGimpsey wrote:
> That code will work in XL97. However, you're probably running it from a
> button, right? If so, you need to set the button's Take Focus On Click
> property to False.
>
> This was fixed in XL00.
>
> In article <422059fa$0$30319$626a14ce@news.free.fr>,
> cyrille <patchaltair@yahoo.fr> wrote:
>
>
>>Hi,
>>I have the following procedure:
>>
>>1 With Worksheets(1).Range("b10:b5000")
>>2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
>>3 If Not C Is Nothing Then
>>4 firstAddress = C.Address
>>5 C.Activate
>>6 End If
>>7 End With
>>
>>It is perfectly working under Excel2000 but not under Excel97. In the
>>latter case, it stops in line 2 (set C =...) and I obtained the
>>following error : (1004)
>>Impossible to read the find property of the range class
>>(My translation of the error message is probably terrible!).
>>Can you help me to write this in a different manner ? The funny thing,
>>this code is given as an example of the Find property in the excel97
>>help file...
>>Thanks in advance.

[cyrille]

I tought I understood but now I'm not so sure...
I tried something like :

Private Sub CommandButtonA_Click()
If Application.OperatingSystem <> "Windows (32-bit) NT 5.00" Then
MsgBox Application.OperatingSystem
CommandButtonA.TakeFocusOnClick = False
End If
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
C.Activate
End If
End With
End Sub

Indeed, with Excel97, the message box is open so I imagine that
CommandButtonA.TakeFocusOnClick property is set to false but I continue
to obtain the same error message (error 1004 : Impossible to read the
find property of the range class).
I suppose I did something wrong... Can you help me ??
Thanks in advance
Cyrille


JE McGimpsey wrote:
> That code will work in XL97. However, you're probably running it from a
> button, right? If so, you need to set the button's Take Focus On Click
> property to False.
>
> This was fixed in XL00.
>
> In article <422059fa$0$30319$626a14ce@news.free.fr>,
> cyrille <patchaltair@yahoo.fr> wrote:
>
>
>>Hi,
>>I have the following procedure:
>>
>>1 With Worksheets(1).Range("b10:b5000")
>>2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
>>3 If Not C Is Nothing Then
>>4 firstAddress = C.Address
>>5 C.Activate
>>6 End If
>>7 End With
>>
>>It is perfectly working under Excel2000 but not under Excel97. In the
>>latter case, it stops in line 2 (set C =...) and I obtained the
>>following error : (1004)
>>Impossible to read the find property of the range class
>>(My translation of the error message is probably terrible!).
>>Can you help me to write this in a different manner ? The funny thing,
>>this code is given as an example of the Find property in the excel97
>>help file...
>>Thanks in advance.

[Dave Peterson]

While in design mode (another button on that control toolbox toolbar),
rightclick on the button and show its properties.

The change the .takefocusonclick property to false. (It's a one time manual
effort.)

Alternatively, you could do this:

Private Sub CommandButtonA_Click()
Activecell.activate '<--
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
C.Activate
End If
End With
End Sub

But now a followup. Is worksheets(1) the same worksheet that holds the button?

If yes, then never mind. But if it's a different worksheet, you'll want to
select/activate that sheet before you activeate that found cell.

Private Sub CommandButtonA_Click()
Activecell.activate '<--
With Worksheets(1).Range("a10:a5000")
Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
c.parent.select '<--
C.Activate
End If
End With
End Sub

cyrille wrote:
>
> I tought I understood but now I'm not so sure...
> I tried something like :
>
> Private Sub CommandButtonA_Click()
> If Application.OperatingSystem <> "Windows (32-bit) NT 5.00" Then
> MsgBox Application.OperatingSystem
> CommandButtonA.TakeFocusOnClick = False
> End If
> With Worksheets(1).Range("a10:a5000")
> Set C = .Find("A", LookIn:=xlValues, Lookat:=xlWhole)
> If Not C Is Nothing Then
> firstAddress = C.Address
> C.Activate
> End If
> End With
> End Sub
>
> Indeed, with Excel97, the message box is open so I imagine that
> CommandButtonA.TakeFocusOnClick property is set to false but I continue
> to obtain the same error message (error 1004 : Impossible to read the
> find property of the range class).
> I suppose I did something wrong... Can you help me ??
> Thanks in advance
> Cyrille
>
> JE McGimpsey wrote:
> > That code will work in XL97. However, you're probably running it from a
> > button, right? If so, you need to set the button's Take Focus On Click
> > property to False.
> >
> > This was fixed in XL00.
> >
> > In article <422059fa$0$30319$626a14ce@news.free.fr>,
> > cyrille <patchaltair@yahoo.fr> wrote:
> >
> >
> >>Hi,
> >>I have the following procedure:
> >>
> >>1 With Worksheets(1).Range("b10:b5000")
> >>2 Set C = .Find("Science", LookIn:=xlValues, Lookat:=xlWhole)
> >>3 If Not C Is Nothing Then
> >>4 firstAddress = C.Address
> >>5 C.Activate
> >>6 End If
> >>7 End With
> >>
> >>It is perfectly working under Excel2000 but not under Excel97. In the
> >>latter case, it stops in line 2 (set C =...) and I obtained the
> >>following error : (1004)
> >>Impossible to read the find property of the range class
> >>(My translation of the error message is probably terrible!).
> >>Can you help me to write this in a different manner ? The funny thing,
> >>this code is given as an example of the Find property in the excel97
> >>help file...
> >>Thanks in advance.

--

Dave Peterson