Complex Sum, Reqd. codes.

[Vikram Dhemare]

Complex Sum, Reqd. codes.
Hi,
I am trying to develop a report, which provides the current inventory
status. There are four sheets named Recpt, Issue, Despatches (as databse) &
StockList (as Summery Statement).
The users are putting the inputs in recpt as receipt of material as per the
consignments received in other words 'Goods recd Notes'.
The sheet is having coulmns like : Doc. No., Date, Part No., Descrption,
Quantity, Supplier name, Remarks.
Second sheet i.e Issue: It is basically for issueance of material to
production line having columns like : Doc. No. (issue slip no.), Date, Part
No. Description, Quantity Issued, Remarks.
The third sheet is Despatches, despatches is being imported from access
database, the date field is in "DD/MM/YYYY HH:MM:SS" format. (here the
evalute funtion doesn't work).
The fourth sheet is the summery sheet where the unique item list (standard
& constant) is there having columns: Part No., Description, Stock Quanity.
Now I am trying to generate the stocks on button click on certain conditions
say if user want to see the stock for any particular date then user have to
give the date via input box.
If user gives the date in input box then the code should search the value
for particular item satisfying the date contions like <=, in recpt, issue as
well as in despatches & put the
result value in summery sheet corresponding row. (All the entries in
database may have repeated, say one part may have recd twice / trice or so
on), then the variable should
store the sum of recpt, issue & despatches.
It is the simple arithmatic formula i.e: Stock as on User defined date =
Reciepts less than particular date)-(Issue less than user defined date +
despatches less than user defined date)
I have tried this by applying application.evaluate(sumproduct funtion). But
the data is huge caused slowed the report geretating process. Is there any
other solution for such report.

I have tried the following codes & getting the results but this is slowed
down the process.
Option Explicit
Dim myCell As Range
Sub Do_Summery()
Dim RequestedDate As Date
Dim ans, ans1, ans2, ans3 As Variant
RequestedDate = InputBox("Enter the report date in DD/MM/YYYY format ",
Format(Now, "DD/MM/YYYY"))
Worksheets("StockList").Range("E1") = "Stock as on " &
Format(RequestedDate, "dd/mm/yyyy")
Application.Calculation = xlCalculationManual
For Each myCell In Worksheets("StockList").Range("InputStockRng")
ans = Application.Evaluate("=sumproduct(('Recpt'!F2:F65500= """ _
& myCell.Value & """ )*('Recpt'!B2:B65500<=" &
CLng(RequestedDate) & ")*('Recpt'!G2:G65500))")
ans1 =
Application.Evaluate("=sumproduct(('Issue'!D265500= """ _
& myCell.Value & """ )*('Issue'!B2:B65500<=" &
CLng(RequestedDate) & ")*('Issue'!F2:F65500))")
'ans2 = Application.Evaluate("=sumproduct(('Desp'!E2:E65500= """ _
& myCell.Value & """ )*(int('Desp'!B2:B65500)=" &
CLng(RequestedDate) & "),('Desp'!J2:J65500))")
ans3 = ans1 + ans2
With myCell
.Offset(0, 3).Value = ans - ans3
End With
Next myCell
Set myCell = Nothing

Application.Calculation = xlCalculationAutomatic
End Sub
Can we store variable like m = sum(if(('Recptuptolastfilledrow= """ _
& myCell.Value & """ )*(Recptuptolastfilledrow<=" &
CLng(RequestedDate) & ")*(Recptuptolastfilledrow))")
and so on. This may run the code faster & provide the desired report in a
fraction of seconds.
Further can we change the format of date field while importing the data into
excel from access. Such as with recordset(Field date)=format("DD/MM/YYYY") &
then copy the recodset to excel file.
Hope, I expressed it correctly. Any help in this regard will be highly
appreciated.

 

[Bob Bridges]

I haven't looked at your code, Vikram, but HOW huge is your data? Is it tens
of thousands of rows, maybe? or even thousands?

 

[ShaneDevenshire]

Hi,

1. Your code would run faster if you didn't reference such a large range.
2. And replacing your loop with something like this could produce
substantial improvements, although you will need to modify my code because I
spent an hour doing it once and then the site trashed my email when I sent it
and I don't have the patients to do it again.

Selection= _

"=SUMPRODUCT(('S1'!F1:F65500=A3)*('S1'!B1:B65500<=RD)*'S1'!G1:G65500)-SUMPRODUCT(('S2'!D165500=A3)*('S2'!B1:B65500<=RD)*'S2'!F1:F65500)-SUMPRODUCT(('S3'!E1:E65500=A3)*(INT('S3'!B1:B65500)=RD)*'S3'!J1:J65500)"
Selection=Selection.Value

I renamed the sheets to simply my typing, I renamed the range RD for
RequestedDate, and I put it in the spreadsheet as a date, but you can adjust
for that or not. A3 represents the first cell of the InputStockRng range.
The selection is the area which your loop cycles through, so in effect there
is no loop the formula is put in all the cells with one command and then
converted to values.

 

[Vikram Dhemare]

Thanks for your quick response.
The data is almost around 40k rows. Is there any other solution except
sumproduct.

Further, what about recordset date field format ?

I know I am nearer to my goal, but also know that without communities help I
may not achieve it.