练习-for和if的初步使用

作者: A_rrow | 来源:发表于2019-06-30 22:24 被阅读0次

修改学生名单，并将修改的名单表逐一取出新建一张表保存

Sub a()
Dim i As Integer
'定义sht为工作表
Dim sht As Worksheet

For Each sht In Sheets
    sht.Select
    
'处理单个表
For i = 100 To 2 Step -1
    If Range("B" & i) = "理工" Then
        Range("C" & i) = "LG"
    ElseIf Range("B" & i) = "文科" Then
        Range("C" & i) = "WK"
    Else
        Range("C" & i) = "CJ"
    End If
    
    If Range("E" & i) = "男" Then
        Range("F" & i) = "先生"
    Else
        Range("F" & i) = "女士"
    End If
    
    If Range("D" & i) = "" Then
        Range("D" & i).Select
        Selection.EntireRow.Delete
    End If
Next

'处理完单个表的内容后，用其内容和表名另存为一个新的工作簿
    sht.Copy
    ActiveWorkbook.SaveAs FileName:="/Users/liupinan/Desktop/temp" & sht.Name & ".xlsx"
    ActiveWorkbook.Close
Next
End Sub