VBA如何分类汇总

quwanyu1024

各位大神：
        VBA分类汇总时如何根据条件再汇总，并在新表中显示，请求帮助（见附件）。谢谢！

quwanyu1024

请大神们帮帮忙！

quwanyu1024

请问“ If (arr(i, 1) = "czx" Or arr(i, 1) = "hsm" Or arr(i, 1) = "mcc" Or arr(i, 1) = "qh" Or arr(i, 1) = "xz" Or arr(i, 1) = "cyj" Or arr(i, 1) = "tj" Or arr(i, 1) = "llm" Or arr(i, 1) = "hlg") And arr(i, 4) Like "*工资" Then                arr(i, 5) = arr(i, 5) - 260             End If”如何用数组表示出来？

iwangyi

Sub CC()
Set cn = CreateObject("adodb.connection")
cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
'Sql = "SELECT 序号,户名,开户行名称,卡/折号,发票事由,金额-300,填报人,填报日期,项目类别,发票张数,附件张数,打款帐户,是否固定资产,备注 FROM [发票收集登记表$a2:n] WHERE 户名 IN ('czx','hsm','mcc','xz','qh','hlg','cyj','tq','llm') AND  发票事由 like '%" & 工资 & "'"
Sql = "SELECT* FROM [发票收集登记表$a2:n] WHERE 户名 IN ('czx','hsm','mcc','xz','qh','hlg','cyj','tq','llm') AND  发票事由 like '%" & 工资 & "'"
With Sheets.Add(after:=Sheets(Sheets.Count))
.Name = "汇总表二"
Rows(1).Copy .Rows(1): Rows(2).Copy .Rows(2)
.Range("a3").CopyFromRecordset cn.Execute(Sql)
End With
End Sub

iwangyi

附件，请参考

魂断蓝桥

Sub 查询()
Dim cnn As Object
Dim Sql As String, sh As Worksheet
Set cnn = CreateObject("ADODB.CONNECTION")
Application.DisplayAlerts = False
For Each sh In Sheets
    If sh.Name <> "发票收集登记表" Then
        sh.Delete
    End If
Next
Application.DisplayAlerts = True
Dim arr, i%, j%, brr, r%
brr = Array("czx", "hsm", "mcc", "xz", "qh", "hlg", "cyj", "tq", "llm")
cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select distinct 户名 from [发票收集登记表$a2:L]  where 打款帐户='工资代发'"
arr = cnn.Execute(Sql).GETROWS
For i = 0 To UBound(arr, 2)
    Sheets.Add after:=Sheets(Sheets.Count)
        With ActiveSheet
            Sheet1.[A1:N2].Copy .[A1]
            .Name = arr(0, i)
                If IsError(Application.Match(.Name, brr, 0)) Then
                        Sql = "SELECT * FROM [发票收集登记表$B2:N] WHERE 户名='" & arr(0, i) & "'"
                         .[b3].CopyFromRecordset cnn.Execute(Sql)
                Else
                    Sql = "SELECT count(*) FROM [发票收集登记表$B2:N] WHERE 户名='" & arr(0, i) & "' and 发票事由 like '%工资' "
                    If cnn.Execute(Sql)(0) > 0 Then
                         Sql = "SELECT * FROM [发票收集登记表$B2:N] WHERE 户名='" & arr(0, i) & "'"
                        .[b3].CopyFromRecordset cnn.Execute(Sql)
                        r = .[b999].End(3).Row
                        .Range("e" & r + 1) = "社保经费": Range("F" & r + 1) = "-300"
                    Else
                        Sql = "SELECT * FROM [发票收集登记表$B2:N] WHERE 户名='" & arr(0, i) & "'"
                        .[b3].CopyFromRecordset cnn.Execute(Sql)
                    End If
                End If
        End With
Next
Set cnn = Nothing
End Sub

iwangyi

魂断蓝桥 发表于 2018-9-13 10:53
Sub 查询()
Dim cnn As Object
Dim Sql As String, sh As Worksheet

sQL+数组学习了

quwanyu1024

谢谢各位大神们的帮助！谢谢！！！都是高手呀！