Compare two strings in vba excel

Question

I need to compare each cell of the column "Chef" to each cell of the column "nom", so that I can find the right "matricule" and store it in "matt", I have tried this this VBA code:

 Sub compare()
Dim p As Integer
    Dim q As Integer
    Dim Nom As String
    Dim Nomchercher As String
    Dim Matr As String

    p = 0
    q = 0

    For j = 1 To 10
        p = p + 1
        Cells.Find("Chef").Select
        ActiveCell.Range("a1").Offset(p, 0).Activate
        ActiveCell.Select
        AdresseTexte1 = ActiveCell.Address(0, 0)
        Nomchercher = Range(AdresseTexte1)

    For i = 1 To 10

        q = q + 1
        Cells.Find("Nom").Select
        ActiveCell.Range("a1").Offset(q, 0).Activate
        AdresseTexte2 = ActiveCell.Address(0, 0)
        Nom = UCase(Range(AdresseTexte2))
        Cells.Find("Matricule").Select
        ActiveCell.Range("a1").Offset(q, 0).Activate
        AdresseTexte3 = ActiveCell.Address(0, 0)
        Matr = UCase(Range(AdresseTexte3))



        Cells.Find("Matt").Select
        ActiveCell.Range("a1").Offset(p, 0).Activate
        Temps = InStr(1, UCase(Nom), UCase(Nomchercher), vbTextCompare)

       If Temps <> 0 Then
       ActiveCell.Value = Matr
       End If
    Next i
    q = 0
    Next j
End Sub

But it gives me this result:

I don't know why it shows "48", Any help?

Have you looked at VLOOKUP? I suspect you get 48 for blank entries as there is no match and 48 is the final cell you loop to in your loop... — Alex P
– Alex P, Commented Jun 9, 2015 at 16:04

Dawid · Accepted Answer · 2015-06-11 09:00:27Z

1

Problem is in InStr Function -> https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspx

Temps = InStr(1, UCase(Nom), UCase(Nomchercher), vbTextCompare)

You search UCase(Nomchercher) in UCase(Nom)

You always find Nom = "" in all data in column Nomchercher

This will works better:

Temps = InStr(1, UCase(Nomchercher), UCase(Nom), vbTextCompare)

Edit: (faster compare)

Sub FasterCompare()

Dim ColMatricule As Integer
Dim ColNom As Integer
Dim ColChef As Integer
Dim ColMatt As Integer
Dim LastRow As Long

ScreenUpdating = False

'find column name
ColMatricule = Cells.Find("Matricule").Column
ColNom = Cells.Find("Nom").Column
ColChef = Cells.Find("Chef").Column
ColMatt = Cells.Find("matt").Column

'find last row of data
LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For j = 2 To LastRow 'chef
    If Cells(j, ColChef) <> vbNullString Then 'if chef is null then dont search nom
        For i = 2 To LastRow 'nom
            If Cells(j, ColChef) = Cells(i, ColNom) Then
            Cells(j, ColMatt) = Cells(i, ColMatricule)
            Exit For
            End If
        Next i
    End If
Next j

ScreenUpdating = True

End Sub

edited Jun 11, 2015 at 9:00

answered Jun 9, 2015 at 16:13

Dawid

7863 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

zenami Over a year ago

It works when the number of rows is low (20 more or less), but when i try it in my actual file which contains 4615 rows, it doesnt, why?

Dawid Over a year ago

you check only 10 first rows For j = 1 To 10 For i = 1 To 10 This can be an issue

zenami Over a year ago

i check For j = 1 To 4615 For i = 1 To 4615

zenami Over a year ago

the corresponding "matt" of each "chef" is not the right "matricule"

Dawid Over a year ago

Only thing that comes to my head, that your code shows the last found Nom, your description of error is too general. Can you add data sorce, where problem appeared?

|

Collectives™ on Stack Overflow

Compare two strings in vba excel

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related