1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| Option Explicit
Sub Test()
Call TransformeValeurEnFormule(Range("E7:E37,M7:M37,J7:J37"))
End Sub
Private Sub TransformeValeurEnFormule(rng As Range)
Dim cel As Range
Dim B As Boolean
Dim D As Double
For Each cel In rng.Cells
Select Case TypeName(cel.Value)
Case "Empty"
cel.Offset(0, 1).FormulaLocal = ""
Case "Boolean"
B = cel.Value
cel.Offset(0, 1).FormulaLocal = "=" & IIf(B, "VRAI", "FAUX")
Case "Date", "Currency", "Double"
D = cel.Value
cel.Offset(0, 1).FormulaLocal = "=" & D
Case "String"
If cel.NumberFormat <> "@" Then
cel.Offset(0, 1).FormulaLocal = "=""" & Replace(cel.Value, """", """""") & """"
Else
cel.Offset(0, 1).FormulaLocal = cel.FormulaLocal 'Pour pas changer le résultat
End If
Case "Error"
Select Case cel.Value
Case CVErr(xlErrNull) '2000
cel.Offset(0, 1).FormulaLocal = "=#NUL!"
Case CVErr(xlErrDiv0) '2007
cel.Offset(0, 1).FormulaLocal = "=#DIV/0!"
Case CVErr(xlErrValue) '2015
cel.Offset(0, 1).FormulaLocal = "=#VALEUR!"
Case CVErr(xlErrRef) '2023
cel.Offset(0, 1).FormulaLocal = "=#REF!"
Case CVErr(xlErrName) '2029
cel.Offset(0, 1).FormulaLocal = "=#NOM?"
Case CVErr(xlErrNum) '2036
cel.Offset(0, 1).FormulaLocal = "=#NOMBRE!"
Case CVErr(xlErrNA) '2042
cel.Offset(0, 1).FormulaLocal = "=NA()"
End Select
Case Else
cel.Offset(0, 1).FormulaLocal = ""
End Select
Next cel
End Sub |
Partager