Sub EntrerValeurs()
Dim F As String
F = "=IF(RC3="""","""",CHOOSE(MATCH(RC3,{""BTP"";""BMCO"";""PMX"";""BPOP"";""MTTP"";""FRDF"";""FFT"";""ESF"";""""},0),11 650 252,07 050 252,36 000 252,53 332 252,24 152 252, 56 500 S52,79 100 252,86 100 252))"
With Sheets("Sheet1").Range("O2:O" & Sheets("Sheet1").Range("A65536").End(xlUp).Row)
.FormulaR1C1 = F
.Value = .Value 'supprime les formules
End With
End Sub
Sub EntrerValeurs()
Dim F, G As String
F = "=IF(RC3="""","""",CHOOSE(MATCH(RC3,{""BTP"";""BMCO"";""PMX"";""BPOP"";""MTTP"";""FRDF"";""FFT"";""ESF"";""""},0),11 650 252,07 050 252,36 000 252,53 332 252,24 152 252, 56 500 S52,79 100 252,86 100 252))"
G = "=IF(RC3="""","""",CHOOSE(MATCH(RC3,{""BP"";""BCO"";""MX"";""POP"";""MTP"";""FRF"";""FOT"";""EGF"";""""},0),11 650 352,07 050 352,36 000 352,53 332 352,24 152 352, 58 500 S52,79 100 352,86 100 852))"
With Sheets("Sheet1").Range("O2:O" & Sheets("Sheet1").Range("A65536").End(xlUp).Row)
.FormulaR1C1 = F
.FormulaR1C1 = G
.Value = .Value 'supprime les formules
End With
End Sub
Dim F As String, plage As Range
F = "=INDEX({10;11;15;60},MATCH(RC3,{""Toto"";""max"";""martin"";""titi""},0))"
Option Explicit
Sub test()
Dim t1() As Variant, t2() As Variant, c As Range
t1 = Array("toto", "titi", "tata")
t2 = Array(10, 20, 30)
For Each c In Range("C2::C20")
If Application.Match(c, t1, 0) > 0 Then c.Offset(0, 4).Value _
= t2(Application.Match(c, t1, 0) - 1)
Next c
End Sub
Option Base 1
Function MYVAL(MYCEL As Range)
T1 = Array("BTP", "BMCO", "PMX", "BPOP", "MTTP", "FRDF", "FFT", "ESF", "BP", "BCO", _
"MX", "POP", "MTP", "FRF", "FOT", "EGF")
T2 = Array("11 650 252", "07 050 252", "36 000 252", "53 332 252", "24 152 252", _
"56 500 S52", "79 100 252", "86 100 252", "11 650 352", "07 050 352", "36 000 352", _
"53 332 352", "24 152 352", "58 500 S52", "79 100 352", "86 100 852")
x = Application.Match(MYCEL, T1, 0)
If Not IsError(x) Then MYVAL = T2(x)
End Function
Sub d()
Dim rng As Range, c As Range
Set rng = Range("O2:O" & Range("C65000").End(xlUp).Row)
For Each c In rng
If c.Offset(0, -12) <> "" Then c = MYVAL(c.Offset(0, -12))
Next
End Sub
Beaucoup de 29 dans l'histoire...sur mon fichier ça bloque a la ligne 29
Tu l'aurais jeté à la mer ?!Code:Option Base 1