|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim ar As Variant
Dim i As Long, r As Long
Dim d As Object
Set d = CreateObject("scripting.dictionary")
rq = TextBox1.Text
kh = ComboBox1.Text
Set rn = Sheets("送货单模板").Rows("1:20")
With Sheets("送货明细表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 3 Then MsgBox "送货明细表为空!": Exit Sub
ar = .Range("a2:l" & r)
End With
With Sheets("送货单")
.UsedRange.Clear
If rq = "" And kh = "" Then
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If IsDate(ar(i, 1)) Then
zd = ar(i, 1) & "|" & ar(i, 2)
If Not d.exists(zd) Then Set d(zd) = CreateObject("scripting.dictionary")
d(zd)(i) = ""
End If
End If
Next i
ElseIf rq <> "" And kh = "" Then
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If IsDate(ar(i, 1)) Then
If ar(i, 1) = CDate(rq) Then
zd = ar(i, 1) & "|" & ar(i, 2)
If Not d.exists(zd) Then Set d(zd) = CreateObject("scripting.dictionary")
d(zd)(i) = ""
End If
End If
End If
Next i
ElseIf rq <> "" And kh <> "" Then
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If IsDate(ar(i, 1)) Then
If ar(i, 1) = CDate(rq) And ar(i, 2) = kh Then
zd = ar(i, 1) & "|" & ar(i, 2)
If Not d.exists(zd) Then Set d(zd) = CreateObject("scripting.dictionary")
d(zd)(i) = ""
End If
End If
End If
Next i
End If
For Each k In d.keys
n = 0
ReDim br(1 To UBound(ar), 1 To 8)
For Each kk In d(k).keys
n = n + 1
br(n, 1) = n
For j = 5 To 11
br(n, j - 3) = ar(kk, j)
Next j
hd = ar(kk, 12)
rqq = ar(kk, 1)
khh = ar(kk, 2)
Next kk
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 2
If rs = 3 Then
rs = 1
Else
rs = rs
End If
rn.Copy .Cells(rs, 1)
.Cells(rs + 1, 7) = dh
.Cells(rs + 2, 2) = khh
.Cells(rs + 2, 6) = rqq
.Cells(rs + 4, 1).Resize(n, UBound(br, 2)) = br
Next k
End With
Application.ScreenUpdating = True
MsgBox "送货单生成完毕!"
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim ar As Variant
Dim i As Long, r As Long
Dim d As Object
Set d = CreateObject("scripting.dictionary")
rq = TextBox1.Text
If rq = "" Then Exit Sub
If KeyCode = 13 Then
If Not IsDate(CDate(rq)) Then Exit Sub
With Sheets("送货明细表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 3 Then MsgBox "送货明细表为空!": Exit Sub
ar = .Range("a2:l" & r)
End With
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
If IsDate(ar(i, 1)) Then
If ar(i, 1) = CDate(rq) Then
d(ar(i, 2)) = ""
End If
End If
End If
Next i
ComboBox1.List = d.keys
ComboBox1.SetFocus
End If
End Sub
Private Sub UserForm_Initialize()
Dim ar As Variant
Dim i As Long, r As Long
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("送货明细表")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 3 Then MsgBox "送货明细表为空!": Exit Sub
ar = .Range("a2:l" & r)
End With
For i = 2 To UBound(ar)
If ar(i, 2) <> "" Then
d(ar(i, 2)) = ""
End If
Next i
ComboBox1.List = d.keys
End Sub
|
|