【VBA】データ入力と時間計測

'******特定履歴シート******

Sub DP_shukei()
    
'★★実際のシート列で要調整★★
    
    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("特定履歴").Cells(Rows.Count, 1).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("特定履歴").Cells(xlLastRow, 3).End(xlUp).Row   'C列の最終行を取得


'各DP結果の合計
    Range("D1") = WorksheetFunction.CountA(Worksheets("特定履歴").Range(Worksheets("特定履歴").Cells(3, 4), Worksheets("特定履歴").Cells(LastRow, 4)))
    Range("E1") = WorksheetFunction.CountA(Worksheets("特定履歴").Range(Worksheets("特定履歴").Cells(3, 5), Worksheets("特定履歴").Cells(LastRow, 5)))
    Range("F1") = WorksheetFunction.CountA(Worksheets("特定履歴").Range(Worksheets("特定履歴").Cells(3, 6), Worksheets("特定履歴").Cells(LastRow, 6)))
    Range("G1") = WorksheetFunction.CountA(Worksheets("特定履歴").Range(Worksheets("特定履歴").Cells(3, 7), Worksheets("特定履歴").Cells(LastRow, 7)))


End Sub



'******スキャンシート******


Private Sub Worksheet_Change(ByVal Target As Range)

'★★実際のシート列で要調整★★

    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("DP稼働時間").Cells(Rows.Count, 1).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("DP稼働時間").Cells(xlLastRow, 1).End(xlUp).Row   'A列の最終行を取得


'セル値が変更されたときに処理を実行(★★実際のシートセルに要調整★★)
    If Not Intersect(Target, Range("B2:C2")) Is Nothing Then
    
        If Range("B2") <> "" And Range("C2") <> "" Then
                    
            Range("A1") = Format(Now, "c")
            Worksheets("DP結果入力").Select
            
            Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = Format(Now, "c")
        End If
         
    End If

End Sub


Sub tokutei_rireki_input(ByVal MAGI_ID, ByVal now_time, ByVal global_ID, ByVal DP_kekka)
 
'★★実際のシート列で要調整★★
 
    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("特定履歴").Cells(Rows.Count, 1).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("特定履歴").Cells(xlLastRow, 3).End(xlUp).Row   'C列の最終行を取得
    
    Worksheets("特定履歴").Cells(LastRow + 1, 1) = MAGI_ID
    Worksheets("特定履歴").Cells(LastRow + 1, 2) = now_time
    Worksheets("特定履歴").Cells(LastRow + 1, 3) = global_ID


    Select Case DP_kekka

        Case 1
        Worksheets("特定履歴").Cells(LastRow + 1, 4) = "1回目完結"
        
        Case 2
        Worksheets("特定履歴").Cells(LastRow + 1, 5) = "一部DP OK"
        
        Case 3
        Worksheets("特定履歴").Cells(LastRow + 1, 6) = "DP NG"
        
        Case 4
        Worksheets("特定履歴").Cells(LastRow + 1, 7) = "DP不可"
        
        Case Else
        Worksheets("DP結果入力").Select
        
    End Select
    
End Sub



'******DB結果入力シート******

Private Sub CommandButton1_Click()
'1回目完結ボタン

    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("DP稼働時間").Cells(Rows.Count, 2).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("DP稼働時間").Cells(xlLastRow, 2).End(xlUp).Row   'B列の最終行を取得
    
    
    If Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = "" Then
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = ""
    
        Worksheets("スキャン").Select
        
    Else
    
        Call Worksheets("スキャン").tokutei_rireki_input(Worksheets("スキャン").Range("C2"), CDate(Worksheets("スキャン").Range("A1")), Worksheets("スキャン").Range("B2"), 1)

        Call Worksheets("特定履歴").DP_shukei
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = Format(Now, "c")
       
        Worksheets("スキャン").Range("A1") = ""
        Worksheets("スキャン").Select
    
    End If


End Sub

Private Sub CommandButton2_Click()
'一部DBOKボタン

    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("DP稼働時間").Cells(Rows.Count, 2).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("DP稼働時間").Cells(xlLastRow, 2).End(xlUp).Row   'B列の最終行を取得
    
    If Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = "" Then
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = ""
    
        Worksheets("スキャン").Select
        
    Else
    
        Call Worksheets("スキャン").tokutei_rireki_input(Worksheets("スキャン").Range("C2"), CDate(Worksheets("スキャン").Range("A1")), Worksheets("スキャン").Range("B2"), 2)

        Call Worksheets("特定履歴").DP_shukei
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = Format(Now, "c")

        Worksheets("スキャン").Range("A1") = ""
        Worksheets("スキャン").Select

    End If

End Sub


Private Sub CommandButton3_Click()
'DB検品NGボタン

    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("DP稼働時間").Cells(Rows.Count, 2).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("DP稼働時間").Cells(xlLastRow, 2).End(xlUp).Row   'B列の最終行を取得
    
    If Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = "" Then
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = ""
    
        Worksheets("スキャン").Select
        
    Else

        Call Worksheets("スキャン").tokutei_rireki_input(Worksheets("スキャン").Range("C2"), CDate(Worksheets("スキャン").Range("A1")), Worksheets("スキャン").Range("B2"), 3)

        Call Worksheets("特定履歴").DP_shukei
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = Format(Now, "c")

        Worksheets("スキャン").Range("A1") = ""
        Worksheets("スキャン").Select

    End If

End Sub

Private Sub CommandButton4_Click()
'DB検品できないボタン

    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Worksheets("DP稼働時間").Cells(Rows.Count, 2).Row  'Excelの最終行を取得
    
    LastRow = Worksheets("DP稼働時間").Cells(xlLastRow, 2).End(xlUp).Row   'B列の最終行を取得
    
    If Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = "" Then
    
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = ""
    
        Worksheets("スキャン").Select
        
    ElseIf Worksheets("DP稼働時間").Cells(LastRow + 1, 1) <> "" Then

        Worksheets("DP稼働時間").Cells(LastRow + 1, 1) = ""
        Worksheets("DP稼働時間").Cells(LastRow + 1, 2) = ""

        Call Worksheets("スキャン").tokutei_rireki_input(Worksheets("スキャン").Range("C2"), CDate(Worksheets("スキャン").Range("A1")), Worksheets("スキャン").Range("B2"), 4)
     
        Call Worksheets("特定履歴").DP_shukei

        Worksheets("スキャン").Range("A1") = ""

        Worksheets("スキャン").Select

    Else

        Worksheets("スキャン").Select
    
    End If


End Sub