Exact Match Function when using XLFind

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Exact Match Function when using XLFind

Post by Neib74656 » Wed Feb 16, 2022 6:33 pm

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!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Exact Match Function when using XLFind

Post by Dorian (MJT support) » Wed Feb 16, 2022 8:46 pm

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.
Yes, we have a Custom Scripting Service. Message me or go here

Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Re: Exact Match Function when using XLFind

Post by Neib74656 » Wed Feb 16, 2022 11:21 pm

Thanks Dorian!

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

Image

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!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Exact Match Function when using XLFind

Post by Dorian (MJT support) » Thu Feb 17, 2022 8:46 am

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.
Yes, we have a Custom Scripting Service. Message me or go here

Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Re: Exact Match Function when using XLFind

Post by Neib74656 » Sat Feb 26, 2022 7:04 pm

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?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Exact Match Function when using XLFind

Post by Dorian (MJT support) » Sat Feb 26, 2022 9:37 pm

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
*/
Yes, we have a Custom Scripting Service. Message me or go here

Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Re: Exact Match Function when using XLFind

Post by Neib74656 » Wed Mar 09, 2022 4:02 am

Works like a charm thank you very much!

Guess it is time for me to learn some VBA :)

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts