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