使用CurrentRegion返回当前活动区域,相当于在Excel工作表中选择菜单“编辑——定位”命令,在弹出的“定位”对话框中单击“定位条件”按钮,然后在“定位条件”对话框中选中“当前区域”选项按钮,或者相当于使用Ctrl Shift *组合键。
1Sub currentRegion练习1()
2Sheets('sheet1').Range('A1').CurrentRegion.Select
3End Sub
1Sub CurrentRegion()
2 Sheets('sheet1').Range('A1'). CurrentRegion .Copy Sheets.Add.Range('A1')
3End Sub
上面的代码可以复制当前区域到新建工作表中
1Sub CurrentRegion练习3()
2 Dim Crange As Range
3 Set Crange = Sheets('sheet1').Range('A1'). CurrentRegion
4 Crange.Sort key1:=Crange.Cells(2, 5),_
order1:=xlDescending, header:=xlYes
5End Sub
上面的代码对活动数据区域第2行第5列按降序排序
任务:如上图所示按照接听量不同赋予不同的奖金系数,奖金=接听量*系数*单通价格,用VBA计算人员应得奖金。
代码如下:
1Dim lLastrow As Long
2Sub bonus()
3Dim rng As Range
4lLastrow = Range('A1').CurrentRegion.Rows.Count
5Range('G3:H' & lLastrow).ClearContents
6Call getExtraNum
7Call CalculateExtra
8Set rng = Range('G3:h' & lLastrow)
9End Sub
10'获取奖金系数
11Sub getExtraNum()
12 Dim i As Long, ExtraNum As Single
13 For i = 3 To lLastrow
14 ExtraNum = Range('e' & i).Value
15 Range('G' & i).Value = GetExtra(ExtraNum)
16 Next i
17End Sub
18'计算奖金
19Sub CalculateExtra()
20 Range('H3:H' & lLastrow).FormulaR1C1 = '=rc5*rc6*rc7'
21End Sub
22'系数判断
23Function GetExtra(num As Single) As Single
24 Select Case num
25 Case Is < 5500
26 GetExtra = 1
27 Case 5500 To 6000
28 GetExtra = 1.05
29 Case 6000 To 6500
30 GetExtra = 1.1
31 Case Is > 6500
32 GetExtra = 1.12
33 Case Else
34 GetExtra = 0
35 End Select
36End Function
联系客服