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?
Problem is in
InStr
Function -> https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspxTemps = InStr(1, UCase(Nom), UCase(Nomchercher), vbTextCompare)
You search
UCase(Nomchercher)
inUCase(Nom)
You always find
Nom = ""
in all data in columnNomchercher
This will works better:
Temps = InStr(1, UCase(Nomchercher), UCase(Nom), vbTextCompare)
Edit: (faster compare)