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
|
Sub Ranger()
Dim i As Long, j As Integer
Dim t As Range
Dim WSS As Worksheet, WSD As Worksheet
Workbooks.Open Filename:=chemin
Set WSS = ActiveSheet
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Mid(Cells(i, 1), 3, 1) = "=" Then
Cells(i - 1, 1) = Cells(i - 1, 1) & " " & Cells(i, 1)
Rows(i).Delete
End If
Next i
Range("A1", Range("A1").End(xlDown).Address).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlNone, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=True, _
OtherChar:="=", _
DecimalSeparator:="."
Set WSD = Worksheets.Add
For i = 1 To WSS.Cells(Rows.Count, 1).End(xlUp).Row
WSD.Cells(i + 1, 1).Value = WSS.Cells(i, 1).Value
j = 2
While WSS.Cells(i, j).Value <> ""
Set t = WSD.Range("A1", Cells(1, Columns.Count).End(xlToLeft).Address).Find(WSS.Cells(i, j))
If t Is Nothing Then
Set t = WSD.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
Debug.Print t.Address
t.Value = WSS.Cells(i, j).Value
End If
WSD.Cells(i + 1, t.Column).Value = WSS.Cells(i, j + 1).Value
j = j + 2
Wend
Next i
Application.DisplayAlerts = False
WSS.Delete
Application.DisplayAlerts = True
End Sub |
