Macro not finding partial matches

[Ochimus]

User enters either a First Name in D4, Family Name in D6 or Project in Col D9 of the Input Form

For this example Database has variations on two Project names in Col C, but the "real" file can also have variations in First or Family Names.

Need Macro to search each Database row. If it finds a record with a partial or whole match to whatever is in Col D, it should copy the Data to the next free row from L4 downwards.

Unfortunately it isn't doing it, but I can't see why.

Please Login or Register  to view this content.

Any suggestions, pointers, solutions or alternatives welcomed as ever.

Ochimus

[MarvinP]

Hi Ochimus,

Instead of trying to do this problem using VBA, why now learn a bit more about the Advanced Filter that is built into Excel and *Wildcard* searches?

https://www.ablebits.com/office-addi...vanced-filter/

[JohnTopley]

You could use a formula:

in M4

=IFERROR(INDEX(DATABASE!$A$2:$D$10,SMALL(IF((ISNUMBER(SEARCH('INPUT FORM'!$D$4,DATABASE!$A$2:$A$10)))*(ISNUMBER(SEARCH('INPUT FORM'!$D$6,DATABASE!$B$2:$B$10)))*(ISNUMBER(SEARCH('INPUT FORM'!$D$9,DATABASE!$C$2:$C$10))),ROW($A$2:$A$10)-ROW($A$2)+1,""),ROWS($1:1)),COLUMNS($A:A)),"")



Copy across

in L4

=IFERROR(INDEX(DATABASE!$A$2:$D$10,SMALL(IF((ISNUMBER(SEARCH('INPUT FORM'!$D$4,DATABASE!$A$2:$A$10)))*(ISNUMBER(SEARCH('INPUT FORM'!$D$6,DATABASE!$B$2:$B$10)))*(ISNUMBER(SEARCH('INPUT FORM'!$d$9,DATABASE!$C$2:$C$10))),ROW($A$2:$A$10)-ROW($A$2)+1,""),ROWS($1:1)),4),"")

Array formula so enter with C+S+E

EDIT: needs modifying if all 3 are not selected!!!

[JohnTopley]

Updated:

=IFERROR(INDEX(DATABASE!$A$2:$D$10,SMALL(IF((IF($D$4="",1,ISNUMBER(SEARCH('INPUT FORM'!$D$4,DATABASE!$A$2:$A$10))))*(IF($D$6="",1,ISNUMBER(SEARCH('INPUT FORM'!$D$6,DATABASE!$B$2:$B$10))))*(IF($D$9="",1,ISNUMBER(SEARCH('INPUT FORM'!$D$9,DATABASE!$C$2:$C$10)))),ROW($A$2:$A$10)-ROW($A$2)+1,""),ROWS($1:1)),COLUMNS($A:B)),"")

[JohnTopley]

Re your VBA: it does not check for combinations of criteria: if it matches on any one criterion it outputs a record without checking whether other criteria are met.

So if D4=John D6= Smith, it will return all John(s) but NOT Just John Smith

[Ochimus]

First, thanks to both for immediate responses.

Appreciate the Array formula approach and I am used to it, but I want VBA because certain "end users" have a happy habit of overwriting formulae. As the VBA approach just pulls over numbers into each cell, whatever they do can't affect the form next time round.

I also take John's latest point, as the Code as written will stop at the first match, and lists all the matching records. Have to chat to the End User and see if they want "multi- cell" criteria!

So hopefully someone can see the "hole" in the Code and show me what it should say?

Ochimus

[JohnTopley]

InStr parameters are reversed:

If InStr(1, .Range("D4").Value, Sheet2.Range("A" & x).Value, vbTextCompare) > 0

should be

If InStr(1, Sheet2.Range("A" & x).Value, .Range("D4").Value, vbTextCompare) > 0

You also need to check if "input" parameters are blank as blank parameters are selected.

I would suggest you uss the following rather than Sheet1/Sheet2

Please Login or Register  to view this content.

[Ochimus]

Ended up with this Code that checks whether any of three input boxes contain data, then finds and copies across all the records on the Database that completely or partially match whichever cell was filled in.

Posting it here as it may help someone else.

Please Login or Register  to view this content.

May also lead someone to post a more elegant solution that does the job?

Ochimus