|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 计算提成()
Application.ScreenUpdating = False
Dim ar As Variant, br As Variant
Dim cr()
Dim d As Object, dc As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("订台人【提成名单】")
r = .Cells(Rows.Count, 2).End(xlUp).Row
If r < 2 Then MsgBox "订台人【提成名单】工作表为空!": End
ar = .Range("b1:b" & r)
End With
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
d(Trim(ar(i, 1))) = ""
End If
Next i
With Sheets("房号汇总")
rs = .Cells(Rows.Count, 7).End(xlUp).Row
.Range("e2:e" & rs) = Empty
br = Range("c1:g" & rs).Value
ReDim cr(1 To UBound(br), 1 To 1)
For i = 3 To UBound(br)
If Trim(br(i, 1)) <> "" Then
If d.exists(Trim(br(i, 1))) Then
If Trim(br(i, 5)) <> "" Then
If IsNumeric(br(i, 5)) Then
If br(i, 5) >= 500 And br(i, 5) < 1000 Then
cr(i, 1) = 0.08
br(i, 3) = br(i, 5) * 0.08
ElseIf br(i, 5) >= 1000 Then
cr(i, 1) = 0.1
br(i, 3) = br(i, 5) * 0.1
Else
cr(i, 1) = 0
End If
End If
End If
End If
End If
Next i
.Range("n1:n" & rs) = Empty
.[n1].Resize(UBound(cr), 1) = cr
.[e1].Resize(UBound(br), 1) = Application.Index(br, 0, 3)
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|