Find Method in Excel

 Keywords: Search Locate Find FindNext Method Excel Cell String Keyword

Question:

I have the following that does a wonderful job finding a value in an Excel spreadsheet then returning the value in column 5 or E. My problem is that I have discovered the value that I am looking for in column B may be there more than once, so I need to continue searching from the point I found the first instance of the value and returning the value in column 5 of each subsequent appearance of that value I am searching for. I can do it using the for and next, (without using find), but that is extremely slow. Any help that can be offered would be appreciated.
partno = AskLine ("Cross reference", "Enter GM Part Number","" ,0) excelfile = "m:\ptsshrtcuts\durastop.xls" DB=ObjectOpen("Excel.Application") DB.Visible=@false DB.UserControl=@TRUE oAPP=DB.Workbooks oAPP.Open(excelfile) awb = db.activeworkbook awbws = awb.worksheets wksht = "GM CROSSED TO DURASTOP" found = 0 currws = awb.worksheets(wksht) CELLRANGE=DB.Range("b1:b10000") FINDING=CELLRANGE.Find(partno) if FINDING==0 message("","Not Found") goto stop else ;tcell = finding.value ;tcell2 = finding.address tcell3 = finding.row actSheet = db.activesheet thecell = actSheet.Cells(tcell3,5) duranum = thecell.value message("GM %partno%", "Durastop %duranum%") :stop db.quit objectclose(db) exit

Answer:

The documentation for the Find Method states:

You can use the FindNext and FindPrevious methods to repeat the search.

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs,
save the address of the first found cell, and then test each successive found-cell address against this saved address.

http://msdn.microsoft.com/en-us/library/aa195730(office.11).aspx For example:


;Find all cells containing a string
xlsfile = "D:\temp\DATA\test.xls"
srchstr = "h"
objXL             = ObjectCreate("Excel.Application")
objXL.Visible     = @TRUE
objXL.UserControl = @TRUE

objXL.Workbooks.Open(xlsfile)
objRange =  objXL.Worksheets(1).Range("A1:C1000")

list = ''
objAddress = objRange.Find(srchstr)
If objAddress != 0
   ;When the search reaches the end of the specified search range, it wraps around to the beginning of the range.
   ;To stop a search when this wraparound occurs, save the address of the first found cell, and then test each
   ;successive found-cell address against this saved address.
   firstaddress = objAddress.address
   list = firstaddress
   While @TRUE
          objAddress = objRange.FindNext(objAddress)
          If objAddress == 0  || objAddress.address == firstAddress Then Break
          list = list: @TAB : objAddress.address
   EndWhile
EndIf
AskItemlist("Cell Addresses", list , @TAB, @UNSORTED, @SINGLE)
objRange = 0
objAddress = 0
objXL = 0

Article ID:   W18109

Filename:   Find Method in Excel.txt

File Created: 2009:07:02:14:53:32

Last Updated: 2009:07:02:14:53:32

Database Search

TechHome