人员信息表

Sub UpdateExcel()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1) ' 假设在第一个工作表中进行操作

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim col As Integer
    Dim col_j As Integer
    
    Dim col_ryfl As Integer
    
    col = 0

    ' 查找首行中内容为“大区”的单元格并记录列号
    For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        If ws.Cells(1, i).Value = "大区" Then
            col = i
            Exit For
        End If
    Next i

    If col = 0 Then
        MsgBox "未找到“大区”列"
        Exit Sub
    End If

    ' 在“大区”列后插入新列
    ws.Columns(col + 1).Insert Shift:=xlToRight
        Columns("C:H").Select
    Selection.NumberFormatLocal = "G/通用格式"
    ws.Cells(1, col + 1).Value = "大区（新）"
        ws.Columns(col + 2).Insert Shift:=xlToRight
    ws.Cells(1, col + 2).Value = "上级部门名称（新）"
            ws.Columns(col + 3).Insert Shift:=xlToRight
    ws.Cells(1, col + 3).Value = "单元"
    
    
       ws.Columns(col + 4).Insert Shift:=xlToRight
    ws.Cells(1, col + 4).Value = "板块序号"
        ws.Columns(col + 5).Insert Shift:=xlToRight
    ws.Cells(1, col + 5).Value = "板块"
        ws.Columns(col + 6).Insert Shift:=xlToRight
    ws.Cells(1, col + 6).Value = "板块内序号"
    
    
    
    
    
    
    
    
    
    Dim sjp As String
    sjp = ws.Cells.Address(2, 2, 2)
    'ws.Cells(2, col + 1).Formula = "=VLOOKUP(" + sjp + ",[集团公司组织架构表.xls]单元对照表!$C:$F,3,0)"
    ws.Cells(2, col + 1).Formula = "=VLOOKUP(B2,[集团公司组织架构表.xls]单元对照表!$C:$F,3,0)"
    ws.Cells(2, col + 2).Formula = "=VLOOKUP(AG2,[集团公司组织架构表.xls]单元对照表!$C:$F,3,0)"
    ws.Cells(2, col + 3).Formula = "=IFNA(C2,D2)"
    
    ws.Cells(2, col + 4).Formula = "=VLOOKUP(E2,[集团公司组织架构表.xls]单元对照表!$E:$J,3,0)"
    ws.Cells(2, col + 5).Formula = "=VLOOKUP(E2,[集团公司组织架构表.xls]单元对照表!$E:$J,4,0)"
    ws.Cells(2, col + 6).Formula = "=VLOOKUP(E2,[集团公司组织架构表.xls]单元对照表!$E:$J,6,0)"
    
    
    
    For j = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        If ws.Cells(1, j).Value = "上级部门名称" Then
            col_j = j
            Exit For
        End If
    Next j

    If col_j = 0 Then
        MsgBox "未找到“上级部门名称”列"
        Exit Sub
    End If

    ' 在“大区”列后插入新列

   ' ws.Cells(2, col + 2).Formula = "=VLOOKUP(B2,[集团公司组织架构表.xls]单元对照表!$C:$F,3,0)"
    
    
    
    
    
    
    '------人员分类标椎化---------------------
    
    For m = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
      
        If ws.Cells(1, m).Value = "人员分类名称" Then
            col_ryfl = m
            
            
            
    ' 遍历“大区”列中的所有单元格并替换特定值
    For n = 2 To lastRow
        Select Case ws.Cells(n, col_ryfl).Value
            Case "一类合同工", "一类合同工B类", "二类合同工", "二类合同工B类"
                ws.Cells(n, col_ryfl).Value = "合同工"
            Case "一类实习生", "二类实习生"
                ws.Cells(n, col_ryfl).Value = "实习生"
                
            Case "劳务承包（派遣）人员"
                ws.Cells(n, col_ryfl).Value = "劳务"
            Case "离岗/离职"
                ws.Cells(n, col_ryfl).Value = "内退"
           
        End Select
    Next n
    
            
            Exit For
        End If
    Next m

    If col = 0 Then
        MsgBox "未找到“人员分类名称”列"
        Exit Sub
    End If

    '---------------人员分类标椎化----结束----------------------------------


    MsgBox "操作完成"
End Sub