CODEWITHSUNDEEP
vbaexcelcomparestring-comparison
zenami
zenami

Reputation: 131

Compare two strings in vba excel

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:

https://i.sstatic.net/iiPLe.jpg

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

Upvotes: 2

Views: 5987

Answers (1)

Dawid
Dawid

Reputation: 786

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

Upvotes: 1

Related Questions