.Find What lookat:=xlPart works weird returning undesired value

[Tetsurou]

My needs were comparing row by row the value between columns "D" and "B" and if "D" value complete or partial match with "B", copy "E" value to "F".

I found a macro that I modified slightly for testing and it seemed to solve my needs, but after populating it with a large set of different values, I noticed something strange.

If any of the cells "D" repeats the value and there is a match with "B", the value returned from "E" to "F" fails and works strangely, so that, for instance, if (D5) value is "Mad1" and "Mad1" is also in (D2), having matched first (D2) with (B2) returning (E2) value to (F2), when the procedure reaches (D5) doesn't return (E5) value to (F5) but (F2).

I don't know if the instructions I use in the code: "Find(what:=c.Value, LookIn:=xlFormulas, lookat:=xlPart", have that limitation and there is no way to solve it. I have tried including into the Find code: “SearchOrder:=xlByRows, SearchDirection:=xlNext”, but they didn't change anything either.

Before bothering anyone I have tried all the combinations, modifications and approaches that I am capable of and none of them have changed the behaviour. Also searched all posts with similar queries, but this time I give up feeling helpless in the face of what is surely a simple problem (not to my naïve skills). Any help would be welcome (another approach..., for example with instr or whatever), but if possible, I prefer to solve it with VBA and avoid formulas or functions if possible.


Here is the first approach:

Sub Busca_Text_part_MATCH_Copy_Cell()

Dim ws As Worksheet
Dim c As Range, r As Range

Application.ScreenUpdating = False

Set ws = Worksheets("sheet1")

For Each c In ws.Range("D1", ws.Range("D" & Rows.Count).End(xlUp)) 'loop through D
    Set r = ws.Columns(2).Find(what:=c.Value, LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False, SearchFormat:=False)
    If Not r Is Nothing Then  'found in B
        ws.Range("F" & r.Row).Value = c.Offset(, 1).Value 'copy E to F
'        c.Offset(, 2).Value = c.Offset(, 1).Value 'copy E to F  (SAME RESULT)
    End If
Next c

Application.ScreenUpdating = True

End Sub

Behavior OK but when the value is not repeated in column (D)

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm
	A	B	C	D	E	F
1	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA
2	Push	Mad1aser	N5Mad1	Mad1	KKK	KKK
3	No	Aluro		Aluro	Tetsu	Tetsu
4	ho	Geo		Mad12	Geo-Weird
5	Push	Mad4	N5Mad4	Mad4	geo	geo
6	push	Aluro		Aluro2	Aluro-Weird
7	2025-feb-13	Externa	Push	Externa	Geo	Geo
Sheet1

How it must behave even repeating values (highlighted) column (D) but don't.

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm
	A	B	C	D	E	F
1	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA
2	Push	Mad1aser	N5Mad1	Mad1	KKK	KKK
3	No	Aluro		Aluro	Tetsu	Tetsu
4	ho	Geo		Mad12	Geo-Weird
5	Push	Mad4	N5Mad4	Mad4	geo	geo
6	push	Aluro		Aluro	Aluro-Weird	Aluro-Weird
7	2025-feb-13	Externa	Push	Externa	Geo	Geo
Sheet1

How it behaves repeating values (highlighted) in column (D).

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm
	A	B	C	D	E	F
1	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA	CABECERA
2	Push	Mad1aser	N5Mad1	Mad1	KKK	Geo-Weird
3	No	Aluro		Aluro	Tetsu	Aluro-Weird
4	ho	Geo		Mad1	Geo-Weird
5	Push	Mad4	N5Mad4	Mad4	geo	geo
6	push	Aluro		Aluro	Aluro-Weird
7	2025-feb-13	Externa	Push	Externa	Geo	Geo
Sheet1

Thank you in advance for any help.

 

[MARK858]

finding approaches like mine must make you think: what kindergarten did this guy come from?

I don't ever think that as there are normally a few ways of getting to the same end result in code, some are faster, some are easier to read etc. very few have everything in one, at the end of the day it is normally a case of does the code do what you want in a time that is satisfactory to the user and are you comfortable with the code.

We are all learning just at different stages.

As for books I personally like John Walkenbach's style and so I would look at any of his books like Power Programming and of course Bill Jelen has plenty both by himself and others in the MrExcel store.

On a advanced level I am a bit old fashioned and so still go back to Professional Excel Development by Stephen Bullen, Rob Bovey and John Green but that is a bit more technical than you would want probably.

Happy we have given you some options for the question that you are happy with.