.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.

 

[Alex Blakenburg]

Are you only wanting to see if B is contained in D on the same row ? ie B2 look in D2, B3 look in D3 etc.
If so do you need to use a macro ? You could do that with and If Statement.

 

[NoSparks]

Every time the find line executes it starts looking for a partial match in column B starting from the top and uses the first one it finds.

Try this

Sub Busca_Text_part_MATCH_Copy_Cell()

Dim ws As Worksheet
Dim c 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
        If InStr(1, c.Offset(, -2).Value, c.Value) = True Then
            c.Offset(, 2).Value = c.Offset(, 1).Value
        End If
    Next c

Application.ScreenUpdating = True

End Sub

 

[Skyybot]

This exact problem threw me for a loop as well. I can't describe the difference, but I believe I can show it.
The word that you are searching for:
Fruit

xlPart

You will find anything with:

F, Fr, Fru, Frui, Fruit, Fruitful, Fruitfully

xtWhole

You will find only:

Fruit, Fruitful, Fruitfully

At least that is my understanding.

XlLookAt enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

Let me do some testing to verify, or maybe one of the seniors on this forum can elaborate.

 

[Alex Blakenburg]

@Skyybot - That is not correct "xlPart" "Fruit" will find anything containing Fruit. It's effectively doing a find for *Fruit*.
In Example Row 2 - D2 = Mad1 and the OP says that he considers B2 = Mad1aser a match so he does want *Mad1*

PS: xlWhole Fruit would only find Fruit (case sensitivity depends on another field ie MatchCase:=)

 

[Osvaldo Palmeiro]

Maybe something like this.

Sub Busca_Text_part_MATCH_Copy_Cell()
 Dim ws As Worksheet
 Dim c As Range, r As Range
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.FindFormat.Interior.ColorIndex = xlNone
  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, LookAt:=xlPart, SearchFormat:=True)
   If Not r Is Nothing Then  'found in B
    ws.Range("F" & r.Row).Value = c.Offset(, 1).Value 'copy E to F
    r.Interior.Color = vbYellow     'applies interior color yellow in "r" to tag it and so avoid new matches by Find
   End If
  Next c
  Application.FindFormat.Clear
  ws.Columns(2).Interior.ColorIndex = xlNone
End Sub

 

[NoSparks]

In the macro of post 3 change the IF line to omit the = True part

        If InStr(1, c.Offset(, -2).Value, c.Value) Then     'if TRUE

 

[Tetsurou]

First of all, a huge thanx to you all for your welling to help me so quickly.

@ NoSparks
Thank you very much indeed, NoSparks: Your comment about the "find and match" with (B) makes all sense, is one thing what I was struggling with but didn't find way out, anyway. After the modification to your first code, ¡it works like charm!

@Alex Blakenburg:
Thanx for your answer, Alex: Basically, yes, but my problem wasn't with compare and return in a usual way but struggling with complete or partial match, what obviously I was not skillful enougth to reach the goal.

@ Skyybot, thank you mate but was not really the problem I was facing, by the way, Alex Blakenburg’s observations are correct.

@ Osvaldo Palmeiro:
Thank you, Osvaldo, Your solution Works almost perfect, but testing it gambling with source values I’ve found a weird behavior. So I leave a comparison with two results from your code with the sole intention of helping in case someone uses the code and comes across this occurrence. Note that values in columns are not the same that the above for my original post, but is the same pattern:


Osvaldo Palmeiro code working perfect:

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	Mad1	N5Mad1	Mad1	KKK	KKK
3	No	Aluro		Aluro	Aluro	Aluro
4	ho	Geo		Mad1	Geo-Weiird
5	Push	Mad4	N5Mad4	Mad4	geo	geo
6	push	Aluro		Aluro	Aluro	Aluro
7	2025-feb-13	Externa	Push	Externa	Geo	Geo
Sheet1

Osvaldo Palmeiro code working weird with one cell:
1st (D) Mad1 changed for Mad5. It seems that pairs Mad1 (D5) with first Mad1 (B2)

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	Mad1	N5Mad1	Mad5	KKK	Geo-Weiird
3	No	Aluro		Aluro	Aluro	Aluro
4	ho	Geo		Mad1	Geo-Weiird
5	Push	Mad4	N5Mad4	Mad4	geo	geo
6	push	Aluro		Aluro	Aluro	Aluro
7	2025-feb-13	Externa	Push	Externa	Geo	Geo
Sheet1

Again, thank you all guys and sorry for my bad English.

 

[Osvaldo Palmeiro]

1st (D) Mad1 changed for Mad5. It seems that pairs Mad1 (D5) with first Mad1 (B2)

It seems that pairs Mad1 (D5) (D4) with first Mad1 (B2)

Not D5 as you wrote, but D4 matches B2, so E4 goes to F2. It's correct then.

 

[Tetsurou]

It seems that pairs Mad1 (D5) (D4) with first Mad1 (B2)

Not D5 as you wrote, but D4 matches B2, so E4 goes to F2. It's correct then.

Oops... Sorry, yes you’re right. I meant D4, not D5, sorry for the confusion created.

I don't know if I haven't been able to explain it as I wanted, but it shouldn't work as a kind of "lookup", what I was looking for is that, for example, if (D2) is in (B2), (F2) receives the value of (E2) and if not, (E2) remains empty, and so on, just as it happens in the first of the two examples that I have put with your code, where (F2) receives KKK from (E2) and, in the second example, (F2) should remain empty, not with (this time yes) F4 value.

Sorry again, I know it's my english, that is at same level than my vba skills... I hope I have made it clear this time, if not, please feel free to reply to me again and I will see how I can fix it and not still messing it up.

Thanks, Osvaldo, for your time and kindness.