VBA Instr Function Capture Exact Match

[motabrasil]

Hi there
Masters of Excel, I need your help once again. I have a code that it's working almost perfect. However, I just note something is going wrong and I didn't figure out how to fix that. I'm using INSTR Function to search a name inside a group of names and execute some calculation if the string match. The problem here is the names between the groups are a little bit similar and this Instr Function is bringing a wrong data.
Question: Is there any other similar function that brings me the exact match?

Example:

Data Base from sheet6 Column "A".
A1 = Nissan
A2 = Nissancsv, Nissancou
A3 = Nissanrv

LastRow = 2
Do While sheet6.Cells(LastRow, "F") <> ""
strValue(1) = sheet6.Cells(LastRow, "F").Value
If InStr(Sheet6.Cells(lastrow, "A"), strValue(1)) Then
Sheet6.Cells(lastrow, 'B") = Sheet6.Cells(lastrow, "H")
End If
Loop

It seems the Instr Function do not consider those strings as different and put all of these strings in the same box.

Any idea?

Thanks in advance

 

[Luke M]

Assuming you are splitting things at the commas, perhaps this will work?

Do While Sheet6.Cells(lastrow, "F") <> ""
    strValue(1) = Sheet6.Cells(lastrow, "F").Value
    sWords = Split(Sheet6.Cells(lastrow, "A"), ",") 'See VBA help on Split for more info
    If (UBound(Filter(sWords, strValue(1))) > -1) Then
        Sheet6.Cells(lastrow, "B") = Sheet6.Cells(lastrow, "H")
    End If
Loop

Site I found this:
http://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string

 

[motabrasil]

I got the same mistake. Considering the same example of Group of Customers Name as:
Gr1 = Nissan
Gr2 = Nissancsv, Nissancou
Gr3 = Nissanrv

The total of GR1 is correct but the Gr2 and Gr3 is wrong, the code is giving me for Gr2 the total of Gr1 + Gr2 and for Gr3, the total of Gr1 + Gr3.
It seems that if I have a partial match the code consider as 100% match and summarize.
I want to have only when 100% match because I have some customers name very similar but not the same.
Sometimes just one letter different.

Thanks in advance
motabrasil

 

[SirJB7]

Hi, motabrasil!
I think you'll have to return to the old For...Next loop and test each element for complete exactness; Filter isn't suitable for this job since its partial match.
Regards!

 

[motabrasil]

Is there any other Function to replace this "INSTR" that can capture the exact match?
This Function works great when you have different strings. But when they are similar..
doesn't.

 

[SirJB7]

Hi, motabrasil!
If you stick to the INSTR version then give a try to this:

Option Explicit
 
Sub TiredOfLionelMessi()
    Dim LastRow As Long, strValue As String
    LastRow = 2
    Do While Sheet6.Cells(LastRow, "F") <> ""
        strValue = Sheet6.Cells(LastRow, "F").Value & ","
        If InStr(Sheet6.Cells(LastRow, "A") & ",", strValue) Then
            Sheet6.Cells(LastRow, "B") = Sheet6.Cells(LastRow, "H")
        End If
        LastRow = LastRow + 1
    Loop
End Sub

It should give you the exact match required.
Regards!
PS: Changed strValue array by strValue variable, since index is always 1.

 

[motabrasil]

Simple like that!? You rock!!! Again! It works perfect!
Just for curiosity, which other best way I could use considering that the list of names can vary very often and I don't want to change my code every time I got a change?
By the way. Nice name for your code.

Thanks once again

 

[SirJB7]

Hi, motabrasil!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
And about your curiosity, there isn't much more that I can suggest regarding only at that snippet, maybe if you want to post a sample file something else may arise.
Regards!
PS: Despite I agree with Carla Dauden, I've never liked and even less admired our (our? Spanish!) cold chest (will it mean the same as in my local argot?)