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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
| Sub Extract_Date_CMS()
Dim MaCote As String
Dim Rg As New VBScript_RegExp_55.RegExp
Dim Match As VBScript_RegExp_55.Match
Dim Matches As VBScript_RegExp_55.MatchCollection
Dim j, i, h As Double
h = 0
Dim cote, cell, cms_cell, cms_pattern As Range
Dim test As Boolean
Set cms_pattern = Sheets("Berm.CMS").Range(Cells(7, 23), Cells(7, 23).End(xlDown))
Set cote = ActiveSheet.Range(Cells(7, 6), Cells(7, 6).End(xlDown))
Set Rg = CreateObject("Vbscript.RegExp")
For Each cell In cote
For Each cms_cell In cms_pattern
Rg.pattern = cms_cell.Value
Rg.IgnoreCase = True
Set Matches = Rg.Execute(cell.Value)
If Rg.test(cell.Value) Then
Set Match = Matches.Item(0)
On Error Resume Next
i = CDbl(Replace(LCase(Match.SubMatches(1)), "cms", ""))
For j = 1 To Len(Match.SubMatches(0))
If IsNumeric(Mid(Match.SubMatches(0), j, 1)) Then
h = h + 1
cell.offset(0, 3 + h) = CDbl(Val(Mid(Match.SubMatches(0), j, Len(Match.SubMatches(0)) - j + 1)))
j = j + Len(str(cell.offset(0, 5).Value)) - 1
End If
Next
h = 0
cell.offset(0, 6) = i
End If
Next cms_cell
Next cell
Set Rg = Nothing
End Sub |
Partager