Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Sum, Reqd. codes.
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:F655 00= """ _ & myCell.Value & """ )*('Recpt'!B2:B65500<=" & CLng(RequestedDate) & ")*('Recpt'!G2:G65500))") ans1 = Application.Evaluate("=sumproduct(('Issue'!D2:D655 00= """ _ & myCell.Value & """ )*('Issue'!B2:B65500<=" & CLng(RequestedDate) & ")*('Issue'!F2:F65500))") 'ans2 = Application.Evaluate("=sumproduct(('Desp'!E2:E6550 0= """ _ & 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. -- Thanks, Vikram P. Dhemare |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Sum, Reqd. codes.
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Sum, Reqd. codes.
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'!D1:D65500=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. -- Thanks, Shane Devenshire "Vikram Dhemare" wrote: 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:F655 00= """ _ & myCell.Value & """ )*('Recpt'!B2:B65500<=" & CLng(RequestedDate) & ")*('Recpt'!G2:G65500))") ans1 = Application.Evaluate("=sumproduct(('Issue'!D2:D655 00= """ _ & myCell.Value & """ )*('Issue'!B2:B65500<=" & CLng(RequestedDate) & ")*('Issue'!F2:F65500))") 'ans2 = Application.Evaluate("=sumproduct(('Desp'!E2:E6550 0= """ _ & 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. -- Thanks, Vikram P. Dhemare |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Sum, Reqd. codes.
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. -- Thanks, Vikram P. Dhemare "ShaneDevenshire" wrote: 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'!D1:D65500=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. -- Thanks, Shane Devenshire "Vikram Dhemare" wrote: 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:F655 00= """ _ & myCell.Value & """ )*('Recpt'!B2:B65500<=" & CLng(RequestedDate) & ")*('Recpt'!G2:G65500))") ans1 = Application.Evaluate("=sumproduct(('Issue'!D2:D655 00= """ _ & myCell.Value & """ )*('Issue'!B2:B65500<=" & CLng(RequestedDate) & ")*('Issue'!F2:F65500))") 'ans2 = Application.Evaluate("=sumproduct(('Desp'!E2:E6550 0= """ _ & 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. -- Thanks, Vikram P. Dhemare |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Codes Reqd. to create Loading Slip | Excel Discussion (Misc queries) | |||
Windows().activate generating obj reqd msg why? | Excel Programming | |||
Help reqd with adding menu items!! | Excel Programming | |||
Complicated sumproduct help reqd. | Excel Worksheet Functions | |||
formula help reqd please | Excel Programming |