[已解决]求大侠帮忙写一个按要求查找统计数量的代码

tuibian_htf · 发表于 2013-11-11 14:18

求大侠帮忙写一个按要求查找统计数量的代码，在此先谢谢了！！！

最佳答案

hwc2ycy

2013-11-11 16:32

hwc2ycy · 发表于 2013-11-11 16:31

本帖最后由 hwc2ycy 于 2013-11-11 16:37 编辑

Private Sub CommandButton1_Click()
If Not CheckTextbox(Me.TextBox1) Then Exit Sub
If Not CheckTextbox(Me.TextBox2) Then Exit Sub
If Not CheckTextbox(Me.TextBox3) Then Exit Sub
Call range2Listbox(Me.TextBox1.Text & "#" & Me.TextBox2.Text & "#" & Me.TextBox3.Text)
End Sub
Function CheckTextbox(txt As MSForms.TextBox) As Boolean
If Len(txt.Text) = 0 Then
MsgBox "字段未填完整"
txt.SetFocus
Exit Function
End If
CheckTextbox = True
End Function
Private Sub range2Listbox(strCondition As String)
With Me.ListBox1
.Clear
.ColumnCount = 5
End With
Dim arr, strTest, i As Long, lSum As Long
Dim result(), lRecordcount As Long
ReDim result(1 To 5, 0 To 0)
result(1, 0) = "区域"
result(2, 0) = "姓名"
result(3, 0) = "属性"
result(4, 0) = "类别"
result(5, 0) = "数量"
arr = Sheet1.Range("a1").CurrentRegion.Value
For i = LBound(arr) + 1 To UBound(arr)
strTest = arr(i, 3) & "#" & arr(i, 7) & "#" & arr(i, 6)
If strTest = strCondition Then
lSum = arr(i, 9) + lSum
lRecordcount = lRecordcount + 1
ReDim Preserve result(1 To 5, 0 To lRecordcount)
result(1, lRecordcount) = arr(i, 3)
result(2, lRecordcount) = arr(i, 4)
result(3, lRecordcount) = arr(i, 6)
result(4, lRecordcount) = arr(i, 7)
result(5, lRecordcount) = arr(i, 9)
End If
Next
If lRecordcount Then
Me.ListBox1.Column = result
Me.TextBox4.Text = lSum
Else
MsgBox "条件有误，没有查找到合适的数据"
End If
End Sub

复制代码

动态数组做的，没考虑效率之类的。

hwc2ycy · 发表于 2013-11-11 16:32

tuibian_htf · 发表于 2013-11-11 16:42

hwc2ycy 发表于 2013-11-11 16:32

谢谢大侠了

hwc2ycy · 发表于 2013-11-11 17:57

tuibian_htf 发表于 2013-11-11 16:42
谢谢大侠了

其实用ADO也是不错的。

		自动登录	找回密码
密码			注册