Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
VBA has its own Find method. See the VBA help file.
"fred" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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 "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
To add:
I need only to check whether the same data already exists. Fred "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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) "fred" wrote in message ... To add: I need only to check whether the same data already exists. Fred "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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 "Rick Rothstein" wrote in message ... 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) "fred" wrote in message ... To add: I need only to check whether the same data already exists. Fred "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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) "fred" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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) "fred" wrote in message ... To add: I need only to check whether the same data already exists. Fred "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: how to employ Find function?
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 "Rick Rothstein" wrote in message ... 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) "fred" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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) "fred" wrote in message ... To add: I need only to check whether the same data already exists. Fred "KC" wrote in message ... 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? "fred" wrote in message ... 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 "JLGWhiz" wrote in message ... VBA has its own Find method. See the VBA help file. "fred" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to employ ExecuteMso Sub in Excel 2007? | Excel Programming | |||
Do you want to employ an EXCEL VBA program that you developed | Excel Discussion (Misc queries) | |||
where do I find docs on excel automation programming? | Excel Programming | |||
Excel v10 can't find function in C# .Net managed Automation add-in | Excel Programming | |||
Excel Automation Find/Replace Excel 2000 and 2003 | Excel Programming |