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

Tetsurou · Mar 3, 2025

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:

VBA Code:

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

2

Push

Mad1aser

N5Mad1

Mad1

KKK

3

No

Aluro

Tetsu

4

ho

Geo

Mad12

Geo-Weird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

Aluro2

Aluro-Weird

7

2025-feb-13

Externa

Push

Externa

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

2

Push

Mad1aser

N5Mad1

Mad1

KKK

3

No

Aluro

Tetsu

4

ho

Geo

Mad12

Geo-Weird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

Aluro-Weird

7

2025-feb-13

Externa

Push

Externa

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

2

Push

Mad1aser

N5Mad1

Mad1

KKK

Geo-Weird

3

No

Aluro

Tetsu

Aluro-Weird

4

ho

Geo

Mad1

Geo-Weird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

Aluro-Weird

7

2025-feb-13

Externa

Push

Externa

Geo

Sheet1

Thank you in advance for any help.

Tetsurou · Tuesday at 1:50 PM

Tetsurou said:
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.

*E4 value
Sorry i tried edit it but I don't know how nor if it's possible

Osvaldo Palmeiro · Tuesday at 2:16 PM

Hi, @Tetsurou .
I think it would be easier to understand what you need if you please could attach a XL2BB screenshot with some examples, with their expected results, and add the respective explanations of how you've got each result.
Thanks.

Tetsurou · Tuesday at 3:34 PM

Osvaldo Palmeiro said:
Hi, @Tetsurou .
I think it would be easier to understand what you need if you please could attach a XL2BB screenshot with some examples, with their expected results, and add the respective explanations of how you've got each result.
Thanks.

Hi, Osvaldo:
I'm a little afraid that this thread will end up being very confusing (because of me) because, I think that's what I had done, put with examples both what worked and what didn't, but it's obvious that I haven't been able to convey it well. In fact, what I needed is solved by @NoSparks' code and, with yours in the first case that I gave you in the previous XL2BB screenshots.

Ok, I'll paste the examples once again.

Just to have the reference: (D) checks to see if it is in (B) and if it is, (F) will contain the value of (E). If not, (F) must be empty. Of course, in the same row.

Result I am looking for ( in this case I've used @NoSpark's code):

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm

A

B

C

D

E

F

1

CABECERA

2

Push

Mad1

N5Mad1

Mad5

KKK

3

No

Aluro

4

ho

Geo

Mad1

Geo-Weiird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

7

2025-feb-13

Externa

Push

Externa

Geo

Sheet1

Result using your code:

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm

A

B

C

D

E

F

1

CABECERA

2

Push

Mad1

N5Mad1

Mad5

KKK

Geo-Weiird

3

No

Aluro

4

ho

Geo

Mad1

Geo-Weiird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

7

2025-feb-13

Externa

Push

Externa

Geo

Sheet1

Using your code with (D2) value Mad1 instead Mad5 (forcing a right resul):

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm

A

B

C

D

E

F

1

CABECERA

2

Push

Mad1

N5Mad1

Mad1

KKK

3

No

Aluro

4

ho

Geo

Mad1

Geo-Weiird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

7

2025-feb-13

Externa

Push

Externa

Geo

Sheet1

Now using your code, forcing a double error by changing (D3) and (E6). (E6) to see better what value is returning wrong:

TRABAJANDO Excel - BANCO PRUEBAS (Intento inStr)_V.1_Beta.xlsm

A

B

C

D

E

F

1

CABECERA

2

Push

Mad1

N5Mad1

Mad5

KKK

Geo-Weiird

3

No

Aluro

Force error

Aluro

Aluro Weird

4

ho

Geo

Mad1

Geo-Weiird

5

Push

Mad4

N5Mad4

Mad4

geo

6

push

Aluro

Aluro Weird

7

2025-feb-13

Externa

Push

Externa

Geo

Sheet1

I hope I helped you see what I meant. If not, actually, as I've already told you, the question is resolved, but if you need me to explain it better, don't hesitate to keep replying, I'll be happy to give you the explanations you ask me for. The only thing I'm affraid, is becoming this thread in a mess.

Again, thanx for your kindness and patient.

MARK858 · Tuesday at 5:28 PM

@Tetsurou, with what you state as the correct result in your last post it looks like you can just do

VBA Code:

Sub Tetsurou2()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    Set ws = Worksheets("sheet1")

    With ws.Range("F2:F" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(RC[-2]=RC[-4],RC[-1],"""")"
        .Value = .Value
    End With

End Sub

MARK858 · Tuesday at 5:57 PM

Or a partial match as you mentioned earlier

VBA Code:

Sub Tetsurou2b()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    Set ws = Worksheets("sheet1")

    With ws.Range("F2:F" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(COUNTIF(RC[-4],""*""&RC[-2]&""*""),RC[-1],"""")"
        .Value = .Value
    End With

End Sub

Tetsurou · Tuesday at 7:47 PM

MARK858 said:

Or a partial match as you mentioned earlier

VBA Code:

Sub Tetsurou2b()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    Set ws = Worksheets("sheet1")

    With ws.Range("F2:F" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(COUNTIF(RC[-4],""*""&RC[-2]&""*""),RC[-1],"""")"
        .Value = .Value
    End With

End Sub

Thank you, MARK858:

Your codes work perfectly, one for a perfect match and, the second one you wrote (the above one of this post) fits my need perfectly. It's a different approach to @NoSpark's code which also works perfectly.

I have read and tested many of your codes helping other forum members, for me you are a guarantee, a legend. Thanks MARK858.

Of course, I will follow the advice and rules you have given me to be able to communicate properly on the forum.

MARK858, Is there any way to indicate that what I was looking for is already completely solved with your answers (yours and NoSpark's)? I think that by trying to help with a comment on another code I am doing the opposite and confusing everyone due to my poor language skills and way of expressing myself.

For my part, I consider my query satisfied.

Thank you very much indeed.

MARK858 · Tuesday at 8:02 PM

Tetsurou said:
Is there any way to indicate that what I was looking for is already completely solved with your answers (yours and NoSpark's)?

You can't mark 2 posts as being a solution, I would recommend marking the post that has the code you actually use if both work.

Tetsurou · Wednesday at 8:18 AM

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

VBA Code:

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

Thank you very much indeed, NoSparks: after this correction ¡it works like charm!
Using MARK858's code the same results but, I will use your code because, due to my poor skills, is easier for me to control if I need "no case sensitive" by simply adding "vbTextCompare" into this line corrected by you. I consider my query solved.

MARK858 · Wednesday at 7:28 PM

Tetsurou said:
is easier for me to control if I need "no case sensitive"

I think you have it the wrong way round for my post, as what I posted wasn't case sensitive.

Just for completeness if you wanted a case sensitive match using the syntax I posted you could do...

VBA Code:

Sub Tetsurou2C()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    Set ws = Worksheets("sheet1")

    With ws.Range("F2:F" & ws.Range("D" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(ISNUMBER(FIND(RC[-2],RC[-4])),RC[-1],"""")"
        .Value = .Value
    End With

End Sub

Edit: If you wanted it not case sensitive you could also just change the word FIND in the code to the word SEARCH, which would also give a non case sensitive result

Tetsurou · Thursday at 4:25 PM

MARK858 said:
I think you have it the wrong way round for my post, as what I posted wasn't case sensitive.

Just for completeness if you wanted a case sensitive match using the syntax I posted you could do...

VBA Code:

Sub Tetsurou2C() Dim ws As Worksheet Application.ScreenUpdating = False Set ws = Worksheets("sheet1") With ws.Range("F2:F" & ws.Range("D" & Rows.Count).End(xlUp).Row) .FormulaR1C1 = "=IF(ISNUMBER(FIND(RC[-2],RC[-4])),RC[-1],"""")" .Value = .Value End With End Sub

Edit: If you wanted it not case sensitive you could also just change the word FIND in the code to the word SEARCH, which would also give a non case sensitive result

Thank you very much, MARK858, now your and NoSparks's code, works exactly the same, like charm for any of the options I need.

MARK858, really, I bow to you. As I told you, Mark, when I find a question asked on the forum comes close to what I am looking for and I see your participation in the thread (as well as some other forum member that I also have as a reference), I stop to read carefully your proposals trying to learn and understand the logic of the code. I repeat, some of you are legends to me.

I understand that for you and many of those who have been contributing to this forum for years, finding approaches like mine must make you think: what kindergarten did this guy come from? I am a cobol and assembler programmer but my steps in VBA have been by reading all of you, I have not had any learning source but you, so If you can recommend me (apart from keep reading codes) a good textbook that could serve as a bible to understand all the elements well and be a little more self-sufficient (or understand why things fail for me) I'll thank you.

Thank you very much, MARK858, with this what I needed is absolutely resolved and with different approaches.

Cheers.

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

Tetsurou

New Member

Tetsurou

New Member

Osvaldo Palmeiro

Well-known Member

Tetsurou

New Member

MARK858

MrExcel MVP

MARK858

MrExcel MVP

Tetsurou

New Member

MARK858

MrExcel MVP

Tetsurou

New Member

MARK858

MrExcel MVP

Tetsurou

New Member

Similar threads

Share this page

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

New Member

New Member

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock