Excel automation: how to employ Find function?

[fred]

Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[JLGWhiz]

VBA has its own Find method. See the VBA help file.

 

[fred]

I do not use vba but visual basic.
I have already created code to search for data, but it is not instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.

Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[KC]

It appears you already found "2340553" in the set statement. Then you loop
through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.

Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[fred]

Yes,
I am trying to find the fastest way to search for the data.
By looping and checking each cell the time is lost dramatically.
BTW, I am using the full range (65535) and the data is found in the row
32878 after 20 seconds!
Using Find menu from Excel (under Edit menu) the result is displayed
iinstanenously..
How to achieve that using the code?
Thanks,
Fred

It appears you already found "2340553" in the set statement. Then you loop
through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[fred]

To add:
I need only to check whether the same data already exists.
Fred

It appears you already found "2340553" in the set statement. Then you loop
through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[Rick Rothstein]

Your previous post said you were trying to duplicate the speed of the
Edit/Find menu option, but then this posting seems to indicate you are
trying to find duplicates. This seems to contradict what you said in your
other post (or at least I'm not clear on what you are doing)... forget the
timer (you shouldn't be using Find by looping cell by cell the way you have
your loop set up)... can you just tell us what you want your function to
ultimately do (find the first address for the cell with 2340553 in it, or
maybe find the address for the second cell with 2340553 in it, or simply
find that there is more than one cell with 2340553 in it, or something
else)?

--
Rick (MVP - Excel)

To add:
I need only to check whether the same data already exists.
Fred

It appears you already found "2340553" in the set statement. Then you
loop through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[fred]

OK.
I have 2 sheets: Sheet1 and Sheet2.
After selecting (making active) one cell in Sheet2 I need to do fast
comparision (or find?) to check whether the same value exists on Sheet1.
That is all.
And I am interested in to check only the data contained in one selected
column.in Sheet1.
Thanks,
Fred

Your previous post said you were trying to duplicate the speed of the
Edit/Find menu option, but then this posting seems to indicate you are
trying to find duplicates. This seems to contradict what you said in your
other post (or at least I'm not clear on what you are doing)... forget the
timer (you shouldn't be using Find by looping cell by cell the way you
have your loop set up)... can you just tell us what you want your function
to ultimately do (find the first address for the cell with 2340553 in it,
or maybe find the address for the second cell with 2340553 in it, or
simply find that there is more than one cell with 2340553 in it, or
something else)?

--
Rick (MVP - Excel)

To add:
I need only to check whether the same data already exists.
Fred

It appears you already found "2340553" in the set statement. Then you
loop through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[Rick Rothstein]

Okay, I know you said you were not using VBA, so you will have to translate
this VBA code into your VB code...

If Not Worksheets("Sheet1").Cells.Find(ActiveCell.Value) Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

I'm not sure how you would reference the ActiveCell, but I'm guessing the
worksheet change should be this...

If Not moExcelWS.Cells.Find(ActiveCell.Value) Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

Using your posted search string ("2340553"), I'm guessing this would be
it...

If Not moExcelWS.Cells.Find("2340553") Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

--
Rick (MVP - Excel)

OK.
I have 2 sheets: Sheet1 and Sheet2.
After selecting (making active) one cell in Sheet2 I need to do fast
comparision (or find?) to check whether the same value exists on Sheet1.
That is all.
And I am interested in to check only the data contained in one selected
column.in Sheet1.
Thanks,
Fred

Your previous post said you were trying to duplicate the speed of the
Edit/Find menu option, but then this posting seems to indicate you are
trying to find duplicates. This seems to contradict what you said in your
other post (or at least I'm not clear on what you are doing)... forget
the timer (you shouldn't be using Find by looping cell by cell the way
you have your loop set up)... can you just tell us what you want your
function to ultimately do (find the first address for the cell with
2340553 in it, or maybe find the address for the second cell with 2340553
in it, or simply find that there is more than one cell with 2340553 in
it, or something else)?

--
Rick (MVP - Excel)

To add:
I need only to check whether the same data already exists.
Fred

It appears you already found "2340553" in the set statement. Then you
loop through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred

 

[fred]

Thank you very much. That is just what I needed.
I have a follow up question:
what are these parameters: LookIn and LookAt ?
Can I specify the columnNo where to search?
Thanks,
Fred

Okay, I know you said you were not using VBA, so you will have to
translate this VBA code into your VB code...

If Not Worksheets("Sheet1").Cells.Find(ActiveCell.Value) Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

I'm not sure how you would reference the ActiveCell, but I'm guessing the
worksheet change should be this...

If Not moExcelWS.Cells.Find(ActiveCell.Value) Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

Using your posted search string ("2340553"), I'm guessing this would be
it...

If Not moExcelWS.Cells.Find("2340553") Is Nothing Then
MsgBox "A duplicate was found!"
Else
MsgBox "No duplicate was found."
End If

--
Rick (MVP - Excel)

OK.
I have 2 sheets: Sheet1 and Sheet2.
After selecting (making active) one cell in Sheet2 I need to do fast
comparision (or find?) to check whether the same value exists on Sheet1.
That is all.
And I am interested in to check only the data contained in one selected
column.in Sheet1.
Thanks,
Fred

Your previous post said you were trying to duplicate the speed of the
Edit/Find menu option, but then this posting seems to indicate you are
trying to find duplicates. This seems to contradict what you said in
your other post (or at least I'm not clear on what you are doing)...
forget the timer (you shouldn't be using Find by looping cell by cell
the way you have your loop set up)... can you just tell us what you want
your function to ultimately do (find the first address for the cell with
2340553 in it, or maybe find the address for the second cell with
2340553 in it, or simply find that there is more than one cell with
2340553 in it, or something else)?

--
Rick (MVP - Excel)


To add:
I need only to check whether the same data already exists.
Fred

It appears you already found "2340553" in the set statement. Then you
loop through every cell in usedrange to display the time taken.

Am I missing something please?

I do not use vba but visual basic.
I have already created code to search for data, but it is not
instanenous.!
It takes over 20 secs to find the data.
Is that normal?
Below is the code:
==============
Dim c As Range
With moExcelWS.UsedRange
Set c = .Find("2340553")
Start = Timer
For Each c In moExcelWS.UsedRange
If c = "2340553" Then
Debug.Print CStr(Timer - Start)
End If
Next
End With
==============

How can I speed that process? I need to look only in one, preselected
column.
Thanks,
Fred

VBA has its own Find method. See the VBA help file.


Hello,
My app written in vbasic uses Excel automation.
How to implement Find function?
I need to check if the same data exists in specified column.
Using Object Browser I've found this:

Function Find(Arg1 As String, Arg2 As String, [Arg3]) As Double
Member of Excel.WorksheetFunction
but that appears to be not part of Excel automation.

Please advise,
Fred