【VBA】10.2 [emalabo式] ワークブック・ワークシートのコーディングルールをマスターしよう！

Option Explicit

Sub make_elearningunadministeredlist()

    Dim path_unadministeredlist As String
    Dim path_employeelist As String
    Dim path_outputfile As String
    Dim num_row As Long
    
    Application.ScreenUpdating = False

    If Right(ThisWorkbook.Worksheets(1).Range("D3"), 1) <> "\" Then
        path_unadministeredlist = ThisWorkbook.Worksheets(1).Range("D3") & "\" & ThisWorkbook.Worksheets(1).Range("C3")
    Else
        path_unadministeredlist = ThisWorkbook.Worksheets(1).Range("D3") & ThisWorkbook.Worksheets(1).Range("C3")
    End If
    
    If Right(ThisWorkbook.Worksheets(1).Range("D4"), 1) <> "\" Then
        path_employeelist = ThisWorkbook.Worksheets(1).Range("D4") & "\" & ThisWorkbook.Worksheets(1).Range("C4")
    Else
        path_employeelist = ThisWorkbook.Worksheets(1).Range("D4") & ThisWorkbook.Worksheets(1).Range("C4")
    End If
    
    If Right(ThisWorkbook.Worksheets(1).Range("D5"), 1) <> "\" Then
        path_outputfile = ThisWorkbook.Worksheets(1).Range("D5") & "\" & ThisWorkbook.Worksheets(1).Range("C5")
    Else
        path_outputfile = ThisWorkbook.Worksheets(1).Range("D5") & ThisWorkbook.Worksheets(1).Range("C5")
    End If
    
    Workbooks.Open Filename:=path_unadministeredlist
    
    Workbooks.Open Filename:=path_employeelist
    
    Workbooks("eラーニング未受講者一覧ファイル.xlsx").Worksheets(1).Activate
    
    For num_row = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(num_row, 2) = WorksheetFunction.XLookup(ActiveSheet.Cells(num_row, 1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(4), vbNullString)
    
        ActiveSheet.Cells(num_row, 3) = WorksheetFunction.XLookup(ActiveSheet.Cells(num_row, 1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(1), Workbooks("社員情報ファイル.xlsx").Worksheets(1).Columns(2), vbNullString)
    Next num_row
    
    ActiveWorkbook.SaveAs Filename:=path_outputfile
    
    Workbooks("eラーニング未受講者一覧ファイル.xlsx").Close SaveChanges:=False
    Workbooks("社員情報ファイル.xlsx").Close SaveChanges:=True
    
    MsgBox "eラーニング未受講者一覧の作成が完了しました。"
    
    ThisWorkbook.Worksheets(1).Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

Option Explicit

Sub make_elearningunadministeredlist()

    Dim ws_macro As Worksheet
    Dim ws_work As Worksheet
    Dim wb_unadministeredlist As Workbook
    Dim ws_unadministeredlist As Worksheet
    Dim path_unadministeredlist As String
    Dim wb_employeelist As Workbook
    Dim ws_employeelist As Worksheet
    Dim path_employeelist As String
    Dim path_outputfile As String
    Dim num_row As Long
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("eラーニング未受講者一覧作成マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_unadministeredlist = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_unadministeredlist = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    If Right(ws_macro.Range("D4"), 1) <> "\" Then
        path_employeelist = ws_macro.Range("D4") & "\" & ws_macro.Range("C4")
    Else
        path_employeelist = ws_macro.Range("D4") & ws_macro.Range("C4")
    End If
    
    If Right(ws_macro.Range("D5"), 1) <> "\" Then
        path_outputfile = ws_macro.Range("D5") & "\" & ws_macro.Range("C5")
    Else
        path_outputfile = ws_macro.Range("D5") & ws_macro.Range("C5")
    End If
    
    Workbooks.Open Filename:=path_unadministeredlist
    Set wb_unadministeredlist = ActiveWorkbook
    Set ws_unadministeredlist = ActiveWorkbook.Worksheets(1)
        
    Workbooks.Open Filename:=path_employeelist
    Set wb_employeelist = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets(1)
    
    For num_row = 2 To ws_unadministeredlist.Cells(Rows.Count, 1).End(xlUp).Row
        ws_unadministeredlist.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(4), vbNullString)
    
        ws_unadministeredlist.Cells(num_row, 3) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(2), vbNullString)
    Next num_row
    
    wb_unadministeredlist.SaveAs Filename:=path_outputfile
    
    wb_unadministeredlist.Close SaveChanges:=False
    wb_employeelist.Close SaveChanges:=True
    
    MsgBox "eラーニング未受講者一覧の作成が完了しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

    For num_row = 2 To ws_unadministeredlist.Cells(Rows.Count, 1).End(xlUp).Row
        ws_unadministeredlist.Cells(num_row, 2) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(4), vbNullString)
    
        ws_unadministeredlist.Cells(num_row, 3) = WorksheetFunction.XLookup(ws_unadministeredlist.Cells(num_row, 1), ws_employeelist.Columns(1), ws_employeelist.Columns(2), vbNullString)
    Next num_row

    Workbooks.Open Filename:=path_unadministeredlist
    Set wb_unadministeredlist = ActiveWorkbook
    Set ws_unadministeredlist = ActiveWorkbook.Worksheets(1)