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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
| Sub Macro2()
'
' Macro2 Macro
'
Dim y, i As Integer
Application.ScreenUpdating = False
' splitage des données
For i = 6 To 110
If ThisWorkbook.Sheets(1).Range("R" & i).Value <> "" Then
ThisWorkbook.Sheets(1).Range("R" & i).Select
Selection.TextToColumns Destination:=Range("CA" & i), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
TrailingMinusNumbers:=True
End If
Range("CA30").Select
Next
'reconnaissance des données
For i = 6 To 110
For y = 79 To 98
If Range(ThisWorkbook.Sheets("TDS Défauts ").Cells(i & "," & y)) = UCase(Range(ThisWorkbook.Sheets("TDS Défauts ").Cells(i & "," & y))) Then
If Len(Range(Cells(i & "," & y))) = 2 And Len(Range(Cells(i & "," & y + 1))) > 2 And Len(Range(Cells(i & "," & y + 1))) < 5 Then
Range("T" & i).Value = Range(Cells(i & "," & y)).Value & Range(Cells(i & "," & y + 1)).Value
End If
End If
If Range(Cells(i & "," & y)).Value = UCase(Range(Cells(i & "," & y)).Value) Then
If Len(Range(Cells(i & "," & y))) = 6 Then
Range("T" & i).Value = Range(Cells(i & "," & y)).Value
End If
End If
If Range(Cells(i & "," & y)).Value = "local" Or Range(Cells(i & "," & y)).Value = "Local" Or Range(Cells(i & "," & y)).Value = "LOCAL" Then
If Len(Range(Cells(i & "," & y + 1))) = 6 And Range(Cells(i & "," & y + 1)).Value = UCase(Range(Cells(i & "," & y + 1)).Value) Then
Range("T" & i).Value = Range(Cells(i & "," & y + 1)).Value
End If
End If
If Len(Range(Cells(i & "," & y))) = 2 And Len(Range(Cells(i & "," & y + 1))) > 2 And Len(Range(Cells(i & "," & y + 1))) < 5 Then
If Range(Cells(i & "," & y)).Value = UCase(Range(Cells(i & "," & y)).Value) And Range(Cells(i & "," & y + 1)).Value = UCase(Range(Cells(i & "," & y + 1)).Value) Then
Range("T" & i).Value = Range(Cells(i & "," & y)).Value & Range(Cells(i & "," & y + 1)).Value
End If
End If
Next y
Next i
Application.ScreenUpdating = True
End Sub |
Partager