#2
suzan2023-10-24 20:16
Sub 统计消费和充值金额()
Dim wsData As Worksheet Dim wsSummary As Worksheet Dim Name1_lastRow As Long Dim Name2_lastRow As Long Dim i As Integer Dim j As Integer Dim k As Integer Dim totalExpense(3800) As Long Dim totalRecharge(3800) As Long Dim customer(3800) As String Dim bcustomer(23610) As String Dim expense As Long Dim recharge As Long ' 设置数据来源和结果输出的工作表 Set wsData = ThisWorkbook.Sheets("日明细") Set wsSummary = ThisWorkbook.Sheets("余款统计") Set wsName = ThisWorkbook.Sheets("客户名单") ' 清除原有的统计结果 'wsSummary.Range("A2:C" & wsSummary.Cells(Rows.Count, "A").End(xlUp).Row).Clear ' 获取数据最后一行 Name1_lastRow = wsData.Cells(Rows.Count, "A").End(xlUp).Row Name2_lastRow = wsName.Cells(Rows.Count, "A").End(xlUp).Row ' 初始化统计值 For i = 1 To 3710 totalExpense(i) = 0 ' 将数组元素初始化为 0 Next i For i = 1 To 3710 totalRecharge(i) = 0 ' 将数组元素初始化为 0 Next i ' 遍历数据,进行统计 For i = 1 To Name2_lastRow ' 假设第一行是标题,数据从第二行开始 customer(i) = wsName.Cells(i, 1).Value ' 获取客户名称 Next i For i = 1 To 1000 j = 1 For k = 1 To 23610 bcustomer(k) = CStr(wsData.Cells(k + 1, 4).Value) ' 获取客户名假设第一行是标题,数据从第二行开始 If bcustomer(k) = customer(i) Then expense = wsData.Cells(k + 1, 9).Value recharge = wsData.Cells(k + 1, 15).Value totalExpense(i) = totalExpense(i) + expense ' 累加消费金额 totalRecharge(i) = totalRecharge(i) + recharge '累加充值金额 End If Next k Next i For i = 1 To 3710 wsSummary.Cells(i + 1, 1).Value = customer(i) ' 将 arr1 的元素填入第一列 wsSummary.Cells(i + 1, 2).Value = totalExpense(i) ' 将 arr2 的元素填入第二列 wsSummary.Cells(i + 1, 3).Value = totalRecharge(i) '将 arr3 的元素填入第三列 wsSummary.Cells(i + 1, 4).Value = totalRecharge(i) - totalExpense(i) Next i End Sub 我发出完整代码,一直找不出错误。 |
j = 1
For k = 1 To 23610
bcustomer(k) = CStr(wsData.Cells(k + 1, 4).Value) ' 获取客户名假设第一行是标题,数据从第二行开始
If bcustomer(k) = customer(i) Then
expense = wsData.Cells(k + 1, 9).Value
recharge = wsData.Cells(k + 1, 15).Value
totalExpense(i) = totalExpense(i) + expense ' 累加消费金额
totalRecharge(i) = totalRecharge(i) + recharge '累加充值金额
End If
Next k
Next i
运行出现了一个问题,i从1到1000 循环都没有问题,但是如果i从1到3710循环的时候,就会出现 运行错误13 类型不匹配。如果类型不匹配,i在前面也会出现问题是不是?
新手一直卡在这,恳请帮忙,谢谢大家!
[此贴子已经被作者于2023-10-24 20:11编辑过]