macro de test de celulles

Function RechercheTablo(Abréviation)
Dim tablo1 As Variant
Dim tablo2 As Variant
tablo1 = Array("tab", "arm", "buf")
tablo2 = Array("Table", "Armoire", "Buffet")
RechercheTablo = tablo2(Application.Match(Abréviation, tablo1, 0) + (LBound(tablo2) = 0))
End Function

Sub test()
MsgBox "l'équivalent de ""tab"" est " & RechercheTablo("tab")

End Sub

Sub teste()
'avec en A1:A3: tab, arm, buf
'avec en B1:B3 : TABLE,ARMOIRE,BUFFET
Application.ScreenUpdating = False
Dim F As Worksheet: Dim F_2 As Range: Set F = Sheets(1)
Set F_2 = Sheets(2).Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
Dim tablo3() As Variant: Dim tablo4() As Variant
tablo3 = F.Range("A1:A3"): tablo4 = F.Range("B1:B3")
For i = 1 To 3
X = tablo3(i, 1): Z = tablo4(i, 1)
    For Each cell In F_2
        If cell = X Then cell.Offset(0, 1) = Z
    Next: Next
Application.ScreenUpdating = True
End Sub

Function Recherche_RNG_Tablo(Abréviation)
Dim tablo3() As Variant
Dim tablo4() As Variant
tablo3 = Sheets(1).Range("A1:A3").Value
tablo4 = Sheets(1).Range("B1:B3").Value
Recherche_RNG_Tablo = tablo4(Application.Match(Abréviation, tablo3, 0) + (LBound(tablo4) = 0))
End Function

Function Recherche_RNG_Tablo(Abréviation)
'Dim tablo3[COLOR=Red][B]()[/B][/COLOR] As Variant
'Dim tablo4[B][COLOR=Red]()[/COLOR][/B] As Variant
Dim tablo3 As Variant
Dim tablo4 As Variant
tablo3 = Sheets(3).Range("A1:A3").Value
tablo4 = Sheets(3).Range("B1:B3").Value
Recherche_RNG_Tablo = tablo4(Application.Match(Abréviation, tablo3, 0) [COLOR=Blue][B], 1[/B][/COLOR]) 
 'lbound(tablo4) = 1 donc  + LBound(tablo4)=0 inutile
End Function