来自一个VBA新手的求助

酸茄子

各位老师好！现在有如下左边的数据源，需要生成右边的统计表格。想学习一下各位老师用VBA都有什么思路解决。
在此先感谢各位老师的围观解答。

我自己写的代码，请各位老师看下哪错误，运行不起来！！
Sub GenerateNewStatistics()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim newWs As Worksheet

    Set ws = Sheets("Sheet0")

    '将 C 列和 D 列的文本处理为数值
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    For i = 1 To lastRow
        ws.Cells(i, 3).Value = Val(ws.Cells(i, 3).Value)
        ws.Cells(i, 4).Value = Val(ws.Cells(i, 4).Value)
    Next i

    '处理 I 列的时间格式
    lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
    For i = 2 To lastRow
        Dim originalTime As String
        originalTime = ws.Cells(i, 9).Value
        Dim newTime As String
        newTime = Left(originalTime, 10)
        ws.Cells(i, 9).Value = newTime
    Next i

    '创建新的工作表用于统计
    Set newWs = Worksheets.Add

    '计算相同放单用户姓名的放单价合计
    Dim userNames As Object
    Set userNames = CreateObject("Scripting.Dictionary")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        Dim userName As String
        userName = ws.Cells(i, 1).Value
        Dim settlementTime As String
        settlementTime = ws.Cells(i, 9).Value
        If settlementTime = "2024-5-1" Then
            If Not userNames.Exists(userName) Then
                userNames.Add userName, 0
            End If
            userNames(userName) = userNames(userName) + ws.Cells(i, 3).Value
        End If
    Next i

    '在新工作表中输出放单用户姓名和放单价合计
    newWs.Cells(1, 1).Value = "放单用户姓名"
    newWs.Cells(1, 2).Value = "放单价合计"
    i = 2
    For Each Key In userNames.Keys
        newWs.Cells(i, 1).Value = Key
        newWs.Cells(i, 2).Value = userNames(Key)
        i = i + 1
    Next

    '计算接单用户合计
    Dim receivingUserNames As Object
    Set receivingUserNames = CreateObject("Scripting.Dictionary")

    For i = 2 To lastRow
        Dim receivingUserName As String
        receivingUserName = ws.Cells(i, 2).Value
        Dim settlementTime As String
        settlementTime = ws.Cells(i, 9).Value
        If settlementTime = "2024-5-1" Then
            If Not receivingUserNames.Exists(receivingUserName) Then
                receivingUserNames.Add receivingUserName, 0
            End If
            receivingUserNames(receivingUserName) = receivingUserNames(receivingUserName) + (ws.Cells(i, 3).Value - ws.Cells(i, 4).Value)
        End If
    Next i

    '在新工作表中输出接单用户姓名和接单用户合计
    newWs.Cells(1, 4).Value = "接单用户姓名"
    newWs.Cells(1, 5).Value = "接单用户合计"
    i = 2
    For Each Key In receivingUserNames.Keys
        newWs.Cells(i, 4).Value = Key
        newWs.Cells(i, 5).Value = receivingUserNames(Key)
        i = i + 1
    Next
End Sub

酸茄子

各位老师天气热，会在回复最优秀者奖励一杯瑞幸咖啡!！！请大家多多参加回复哦

LIUZHU

汇总表设计得欠规范，修改如下：

酸茄子

LIUZHU 发表于 2024-6-25 15:38
汇总表设计得欠规范，修改如下：

老师，VBA可以自动生成这个表格吗

LIUZHU

执行效果如下

LIUZHU

发的JS板块，自然用JS代码

1. function 统计(){
   
2.         const arr=Range("a1").CurrentRegion.Value2.slice(1);
   
3.         const kh=[...new Set([11,5].map(i=>arr.map(x=>x[i])).flat())];
   
4.         let res=arr.reduce((res,x,i)=>{
   
5.                 let temp=[x[8],...Array(kh.length),x[2]-x[3]];
   
6.                 [[5,3],[11,2]].forEach(([i,j])=>{
   
7.                         let n=res[0].indexOf(x[i]);
   
8.                         temp[n]=x[j];
   
9.                 });
   
10.                 return res.push(temp), res;
    
11.         },[["结算时间",...kh,"手续费收入"]]);
    
12.         res.push(["总计",...Array(res.length).fill(`=SUM(R[-${res.length}]C:R[-1]C)`)]);                //添加合计
    
13.         Range("o5").Resize(res.length, res[0].length).FormulaR1C1=res;
    
14. }

复制代码

LIUZHU

附件，可以用WPS打开测试

LIUZHU

没仔细看这个VBA代码，但是总体感觉写的很繁琐

lcs_2046

完全可以去掉第一个for 循环，将单元格值赋给变量，如果需要，在引用单元格时再将文本转为数值，用cint/clang；lastrow 用了两次，好像是一样的；