FindNext method in excel

[SmplLbV123]

I want to do a find on a value in an existing Excel file and return ALL the cells (row and column #'s) that contain that values. I was able to find an example utilizing ActiveX Find method but this VI only return the (row,column) on the first found value. I tried to use the FindNext method without any success. How do I make it to do multiple Find's on the same value? THANKS!!

[Matt_F]

Hello pas123,

One of the best ways to determine how to use Excel's ActiveX methods is to record a Macro of the operations and then look at its source code. From the VB source code you can piece together the LabVIEW code.

Below is an example of the generated VB code.

---

Sub FindAll()
Cells.Find(What:="text_to_find", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub

---

Reagrds,

Matt F

[Michael_Munroe]

As in Matt's sample Macro, the FindNext function should be provided the ref to the ActiveCell. Try using a shift register inside the loop to feed the ref of the result of the first find to the After input on the FindNext. Be sure that the ref into the top left corner of the FindNext is the same range as the Find function. Be sure to close all references when they are no longer needed. See example gif.

[SmplLbV123]

Thanks for helping converting from macro to labview

[grcutter]

How does this loop exit?

I tried this code but for me Range.Count = 1 always and this while loop wraps back around to the beginning of the range and never exits.

Does the Excel version make a difference?   I'm using Excel for Microsoft 365.

[grcutter]

My workaround is to keep track of the first cell found and compare it to the result of Range.FindNext and exit if these are equal but this seems rather cludgy.