-- 新建表格:#单元格a1-a100全部等于1的代码
    Sub test()

    Dim i As Integer
    For i = 1 To 100
    Range("a" & i) = 1
    Next

    End Sub
-- 新建表格:#单元格a1-a100等于1-100的代码
    Sub test()

    Dim i As Integer
    For i = 1 To 100
    Range("a" & i) = i
    Next

    End Sub
-- 新建表格:#单元格a1-a100等于100-10000的代码    
    Sub shishi()

    Dim i As Integer
    For i = 1 To 100
    Range("a" & i) = i * 100
    Next

    End Sub
-- 新建表格:#每隔七个单元格,单元格颜色变成蓝色
Sub color()   #改颜色设置
Dim i As Integer   #固定格式
For i = 1 To 100 Step 7   #固定格式

Range("A" & i).Select   #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With     #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果

Next   #固定格式
End Sub
-- 新建表格:#B列每隔七个单元格,单元格颜色变成蓝色
Sub color()   #改颜色设置
Dim i As Integer   #固定格式
For i = 1 To 100 Step 7   #固定格式

Range("b" & i*7).Select   #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With     #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果

Next   #固定格式
End Sub
-- 新建表格:#C列1,8,15,......,单元格颜色变成蓝色
Sub color()   #改颜色设置
Dim i As Integer   #固定格式
For i = 1 To 100 Step 7   #固定格式

Range("c" & i*7-6).Select   #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With     #录制宏后粘贴的代码,可对i进行相关操作,从而实现相对应的效果

Next   #固定格式
End Sub

-- 新建表格:#利用VBA代码可以将录制宏使用相对引用所带来的麻烦问题进行解决。

-- #例1表格:将称呼显示出来的功能代码
Sub chenghu()
Dim i As Integer

For i = 2 To 26
    If Range("e" & i) = "" Then
        Range("f" & i) = "先生"
    Else
        Range("f" & i) = "女士"
    End If

Next
End Sub
-- #例1表格:显示性别和处理专业代号
Sub chenghu()
Dim i As Integer

For i = 2 To 26
    -- '处理性别的代码
    If Range("e" & i) = "" Then
        Range("f" & i) = "先生"
    Else
        Range("f" & i) = "女士"
    End If
    -- '处理专业代号
    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
Next

End Sub
-- #例1表格:显示性别、处理专业代号和删除姓名空单元格所在的行
Sub chenghu()
Dim i As Integer

For i = 26 To 2 Step -1  #'需要从下往上删;从上往下删的话还是会出现空单元所在的行
    -- '处理性别的代码
    If Range("e" & i) = "" Then
        Range("f" & i) = "先生"
    Else
        Range("f" & i) = "女士"
    End If
    -- '处理专业代号
    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("d" & i) = "" Then
    Range("D" & i).Select  #利用录制宏(删除单元格所在行)所得出的代码
    Selection.EntireRow.Delete
    End If
Next

End Sub
-- #计算工资条,将字段信息与每个员工的工资信息一一对应
Sub gongzitiao()
Dim i As Integer

For i = 3 To 2000 Step 2
    If Range("A" & i) = "" Then
        Exit For
    End If

    Rows("1:1").Select
    Selection.Copy
    Range("A" & i).Select
    Selection.Insert Shift:=xlDown

Next
End Sub

-- #恢复员工工资信息,字段对应多个员工信息
Sub huifugzt()
Dim i As Integer

For i = 3 To 2000
    If Range("a" & i) = "" Then
        Exit For
    End If

    Range("A" & i).Select
    Selection.EntireRow.Delete

Next
End Sub
-- #计算每个员工需缴纳的税额
Sub geshui()
Dim i As Integer
        #'计算个税代码块
For i = 2 To 12
    If Range("c" & i) - 3500 <= 0 Then
        Range("d" & i) = 0
    ElseIf Range("c" & i) - 3500 > 0 And Range("c" & i) - 3500 <= 1500 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.03
    ElseIf Range("c" & i) - 3500 > 1500 And Range("c" & i) - 3500 <= 4500 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.1 - 105
    ElseIf Range("c" & i) - 3500 > 4500 And Range("c" & i) - 3500 <= 9000 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.2 - 555
    ElseIf Range("c" & i) - 3500 > 9000 And Range("c" & i) - 3500 <= 35000 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.25 - 1005
    ElseIf Range("c" & i) - 3500 > 35000 And Range("c" & i) - 3500 <= 55000 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.3 - 2755
    ElseIf Range("c" & i) - 3500 > 55000 And Range("c" & i) - 3500 <= 80000 Then
        Range("d" & i) = (Range("c2") - 3500) * 0.35 - 5505
    ElseIf Range("c" & i) - 3500 > 80000 Then
        Range("d" & i) = (Range("c" & i) - 3500) * 0.45 - 13505
    End If
Next

End Sub
04-13 02:31