EXCEL VBA限制工作数据批号或者自定义规则完整

本文主要是介绍EXCEL VBA限制工作数据批号或者自定义规则完整，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

EXCEL VBA限制工作数据批号或者自定义规则完整


Private Sub Worksheet_Change(ByVal Target As Range)Dim nRow%, Arr(), cMc$, cPc$, cTxt$, nSum!If Target.Row = 1 Or Target.Column <> 4 Then Exit SubIf Target.CountLarge > 1 Then Exit SubcMc = Target.Offset(0, -1).ValuecPc = Target.ValueIf cMc = "" Or cPc = "" Then Exit SubFor sh = 0 To 1With Sheets(Array("期初", "入库")(sh))nRow = .Range("a1048576").End(xlUp).RowArr = .Range("a1:e" & nRow).ValueEnd WithFor i = 2 To nRowIf Arr(i, 2 + sh) = cMc And Arr(i, 3 + sh) = cPc ThennSum = nSum + Arr(i, 4 + sh)End IfNextNextnRow = Target.Row - 1With MeArr = .Range("a1:e" & nRow).ValueEnd WithFor i = 2 To nRowIf Arr(i, 3) = cMc And Arr(i, 4) = cPc ThennSum = nSum - Arr(i, 5)End IfNextWith Target.Offset(0, 1).Validation.Delete.Add 2, 1, 8, nSum.InputTitle = "最大值".InputMessage = nSumEnd With
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim nRow%, Arr(), cMc$, cTxt$, sh%If Target.Row = 1 Or Target.Column <> 4 Then Exit SubIf Target.CountLarge > 1 Then Exit SubcMc = Target.Offset(0, -1).ValueIf cMc = "" Then Exit SubFor sh = 0 To 1With Sheets(Array("期初", "入库")(sh))nRow = .Range("a1048576").End(xlUp).RowArr = .Range("a1:d" & nRow).ValueEnd WithFor i = 2 To nRowIf Arr(i, 2 + sh) = cMc ThenIf Not cTxt & "," Like "*," & Arr(i, 3 + sh) & ",*" ThencTxt = cTxt & "," & Arr(i, 3 + sh)End IfEnd IfNextNextWith Target.Validation.DeleteIf cTxt <> "" Then .Add 3, 1, 1, cTxtEnd With
End Sub