|
excel97 vs excel2000 (Find instruction)
Hi,
I have the following procedu 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 |
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 , cyrille wrote: Hi, I have the following procedu 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. |
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 , cyrille wrote: Hi, I have the following procedu 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. |
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 , cyrille wrote: Hi, I have the following procedu 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. |
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 , cyrille wrote: Hi, I have the following procedu 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 |
| All times are GMT +1. The time now is 10:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com