Exact Match Function when using XLFind

[Neib74656]

Hello Everyone,

I am having an issue when using Xlfind that it finds the next cell that contains the search value. However, for my purposes I need it to match cell contents exactly.

Is there any function available using the native Excel functions?

I can write a work around in which the script can take the value of the found cell and compare it to what it was searching for if needed and keep searching until it finds an exact match but I am hoping there is a built in option for this.

As always thanks for any assistance with this!

[Dorian (MJT support)]

That's a very interesting question and not something that's risen it's head before. Upon investigation it seems this works the same way CTRL-F would. For example searching for "2" would find every instance of a 2, in order, including 200, 20, etc.

I experimented with this :

Code: Select all

XLFind>xlBook,Sheet1,{"2"},Result

But it still behaves the same. So it seems your workaround would be required.

[Neib74656]

Thanks Dorian!

I know that in the find menu you can check off in the options match entire cell contents as indicated below.



Maybe this is something that could be added to MS in the future?

Will code the work around for now

As always thank you to everyone!

[Dorian (MJT support)]

This is a good suggestion. I'll mention it to dev, also just in case there's a finer point I'm not aware of.

[Neib74656]

Looks like I need further insight with this issue.

I have tried to program in a method for finding exact matches as follows.

Code: Select all

Xlopen>XLFile.xlsx,1,XY
RGB>255,0,0,R
repeat>e
xlfind>XY,Sheet1,PL4,
xlgetselectedcell>XY,D,intRow,intCol
if>D=PL4
xlsetcellcolor>XY,Sheet1,intRow,intCol,R
endif
Until>e

the code is purposely an infinite loop for testing purposes. I then created the excel file with several random cells filled with the following

PL40(Q2), PL41(G6),PL4(A14),PL400(M16),PL46(B28)

The code loops to infinity without issue however it only ever selects the value in Q2. It never sets cell color since the value does not match exactly but it never checks any other cell. This leads me to believe that XLfind is always looking for the value in sequence A1, A2, A3 ETC until it finds a value that matches the search parameter. If I put the value PL4 in cell Q2 it does change cell color as it should but if any partial match comes before the exact match it just sticks to the first match it comes across on every loop.

Any suggestions how to avoid this behaviour so an exact match can be achieved?

[Dorian (MJT support)]

Using this article as a guide, you can create something like this :

Code: Select all

XlOpen>d:\MyFile.xlsx,1,xlBook
Let>Search=PL4

LabeltoVar>Find,VBAcode
XLRunCode>xlBook,VBAcode

/*
Find:
    Cells.Find(What:="%Search%", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _ 
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
*/

[Neib74656]

Works like a charm thank you very much!

Guess it is time for me to learn some VBA