Hi All,
I have a problem with VBA and I also posted in another forum.
Kindy see the original post as below link:
https://www.mrexcel.com/board/thread...n-vba.1243391/
Thank you!
Hi All,
I have a problem with VBA and I also posted in another forum.
Kindy see the original post as below link:
https://www.mrexcel.com/board/thread...n-vba.1243391/
Thank you!
Instead of a download from external site, should attach file to post. Follow instructions at bottom of my post.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Andy23, this is what you could have done and should have done
I have a vba code which need to start generate the data from the specific cell.AS in the table, the data appear in column B,C,D,E, while I want it to appear from column E,F,G,H. Then other column needs to set the formula in VBA to calculate it automatically. Pls see the "wanted Result Table" in attached file.
U will see the wanted result in column B,C,D,K,L,M also needs to create in VBA to show the result.
Thank you!
Pls download the link below for the data file.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Have you step debugged? That looping and ReDimming makes my head spin. Consider this alternative.
I had to remove blank lines above and below data rows of Details sheet.Sub AggData() Dim cn As Object, rs As Object Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.FullName & ";" & _ "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text""" rs.Open "SELECT [Date], " & _ "Sum(Amount) AS Amt, First(CntSR) AS Cnt, Sum([Unit]) AS Unt " & _ "FROM [Details$] INNER JOIN (SELECT [Date] AS Dte, Count([Sys Receipt]) AS CntSR FROM (SELECT DISTINCT [Sys Receipt], [Date] FROM [Details$]) GROUP BY [Date]) AS Q1 " & _ "ON [Details$].[Date] = Q1.[Dte] " & _ "GROUP BY [Date] ORDER BY [Date]", cn, 3, 3, 1 Worksheets("Summary").Range("E26").CopyFromRecordset rs End Sub
Took a closer look at your original code.
Change line .DataBodyRange.Columns("A:D") = W to .DataBodyRange.Columns("D:G") = W
It works, which seems odd because date falls under column E and nothing populates column D.
Last edited by June7; 08-18-2023 at 10:53 PM.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.