-- 新建表格:#单元格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