Option Explicit
Sub F_molecule()
Dim plg As Range, DerLig As Long, DerCol As Long, plgMol As Range
Dim Cel As Range, Air As Range, cl As Long
With Sheets("information")
'derniere ligne non vide
DerLig = .Range("A1").CurrentRegion.Find("*", , , , xlByRows, xlPrevious).Row
DerCol = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column
Set plg = .Range(.Cells(1, 2), .Cells(DerLig, DerCol))
End With
With Sheets("classement")
Set plgMol = .Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
End With
For Each Cel In plgMol
If Cel <> "" Then
Set Air = plg.Find(Cel, LookAt:=xlWhole)
If Not Air Is Nothing Then
cl = Air.Column 'numero de colonne
Cel.Offset(0, 1) = Sheets("information").Cells(1, cl).Value
End If
End If
Next Cel
'vidage des variables
Set plgMol = Nothing
Set Air = Nothing
Set plg = Nothing
End Sub