Next Help to fix VBA please?

[Andy23]

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/threads/please-help-to-fix-specific-cell-value-and-calculation-in-vba.1243391/

Thank you!

[June7]

Instead of a download from external site, should attach file to post. Follow instructions at bottom of my post.

[Aussiebear]

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.

[June7]

Have you step debugged? That looping and ReDimming makes my head spin. Consider this alternative.


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

I had to remove blank lines above and below data rows of Details sheet.

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.