Sub 文字置き換え()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5p As Worksheet
Dim ws6p As Worksheet
Dim w As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CE1")
Set ws2 = wb1.Worksheets("CE2")
Set ws3 = wb1.Worksheets("CE3")
Set ws4 = wb1.Worksheets("CE4")
Set ws5p = wb1.Worksheets("置換パターン")
Set ws6p = wb1.Worksheets("ログパターン")
For Each w In Worksheets
If w.Name = "CE1" Or w.Name = "CE2" Or w.Name = "CE3" Or w.Name = "CE4" Then
w.Select
lastrow = w.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
str1 = w.Cells(i, 1).Value
' w.Cells(i, 2).Value = w.Cells(i, 1).Value
For j = 1 To 3
w.Cells(i, 1).Value = Replace(w.Cells(i, 1).Value, ws5p.Cells(j, 1).Value, ws5p.Cells(j, 2).Value)
Next j
Next i
End If
Next
End Sub
+++++++++++++++++++++++++
++++++++++++++++++++++++
Sub TL経過時間()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5p As Worksheet
Dim ws6p As Worksheet
Dim w As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CE1")
Set ws2 = wb1.Worksheets("CE2")
Set ws3 = wb1.Worksheets("CE3")
Set ws4 = wb1.Worksheets("CE4")
Set ws5p = wb1.Worksheets("置換パターン")
Set ws6p = wb1.Worksheets("ログパターン")
Dim te2 As Variant
Dim te1 As String
For Each w In Worksheets
If w.Name = "CE1" Or w.Name = "CE2" Or w.Name = "CE3" Or w.Name = "CE4" Then
w.Select
lastrow = w.Cells(Rows.Count, 1).End(xlUp).Row
For m = 1 To lastrow
str1 = w.Cells(m, 1).Value
If str1 <> "" Then
w.Cells(m, 2).Value = w.Cells(m, 1).Value
' For m2 = 1 To 3
' w.Cells(m, 2).Value = Replace(w.Cells(m, 2).Value, ws5p.Cells(m2, 1).Value, ws5p.Cells(m2, 2).Value)
' Next m2
End If
Next m
For j = 1 To 3
PT1 = ws6p.Cells(j, 1).Value
' MsgBox PT1
For i = 1 To lastrow
str1all = w.Cells(i, 2).Value
If str1all <> "" Then
te1 = Trim(str1all)
If Left(te1, 1) = "*" Then
te1 = Mid(te1, 2)
End If
te2 = Split(te1, ":")
temp22 = Left(te2(2), 2)
str1time = te2(0) & ":" & te2(1) & ":" & temp22
'MsgBox str1time
'MsgBox str1all
If str1all Like PT1 Then
If str1all Like "*down*" Then
fpt1 = 1
time1d = CDate(str1time)
w.Cells(i, 2).Interior.ColorIndex = ws6p.Cells(j, 2).Value
w.Cells(i, 1).Interior.ColorIndex = ws6p.Cells(j, 2).Value
ElseIf fpt1 = 1 And str1all Like "*up*" Then
' MsgBox "hit"
fpt1 = 0
time1u = CDate(str1time)
' MsgBox time1u & " " & time1d
PT1elapseDay = Int(time1u - time1d)
PT1elapseTime = TimeValue(CDate(time1u - time1d))
'MsgBox "経過時間 " & PT1elapseDay & "日と" & PT1elapseTime
w.Cells(i, 3).Value = "断時間 " & PT1elapseDay & "日と" & PT1elapseTime
w.Cells(i, 2).Interior.ColorIndex = ws6p.Cells(j, 2).Value
w.Cells(i, 1).Interior.ColorIndex = ws6p.Cells(j, 2).Value
End If
End If
End If
Next i
Next j
End If
Next
End Sub
+++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++
Sub 文字クリア()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5p As Worksheet
Dim ws6p As Worksheet
Dim w As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CE1")
Set ws2 = wb1.Worksheets("CE2")
Set ws3 = wb1.Worksheets("CE3")
Set ws4 = wb1.Worksheets("CE4")
Set ws5p = wb1.Worksheets("置換パターン")
Set ws6p = wb1.Worksheets("ログパターン")
For Each w In Worksheets
If w.Name = "CE1" Or w.Name = "CE2" Or w.Name = "CE3" Or w.Name = "CE4" Then
w.Select
lastrow = w.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
w.Cells(i, 1).Value = ""
w.Cells(i, 2).Value = ""
w.Cells(i, 3).Value = ""
w.Cells(i, 1).Interior.ColorIndex = 2
w.Cells(i, 2).Interior.ColorIndex = 2
Next i
End If
Next
End Sub