Nokia C3: I need code for last purchase or transaction Date for my inventory system

I need code for last purchase or transaction Date for my inventory system
I work for a supermarket and my job is to manage inventory of the Supermarket. I just want to add some more information in my inventory data I guess whoever knows the coding will be able to help me. Let me give you guys my problem description In my inventory data column are as itemcode, purchasedate, quantity, amount and I have another sheet in which I just have item_code and in that I want to add last purchase date, no. of purchase and total purchase. If you know code for this or any related please post it here, Thank you.
Reply With Quote
#2
Old 04-04-2011
GeforceUser GeforceUser is offline
Member

Join Date: Mar 2009
Posts: 1,364
Re: I need code for last purchase or transaction Date for my inventory system
I have done a project for inventory, but it’s hard to tell you without knowing the code for your inventory or sample book. It will be better for me if you post your sample book and others will also easily understand that what changes or code they have to add. In my project I used Max function to get the last transaction date for products in medical, so I think at least you should try Max function to find the last date. And do tell me if it worked for you or not.
Reply With Quote
#3
Old 04-04-2011
Max-well} Max-well} is offline
Member

Join Date: Mar 2011
Posts: 57
Re: I need code for last purchase or transaction Date for my inventory system
Thanks mate for such a quick reply, I never thought I will get reply so fast. I tried your solution but it didn’t worked for me, May be coding concept behind the your medical project and mine inventory system is different. In my inventory system I have to use Max function & sumproduct in macro as
Code:

worksheetfunction.SumProduct(.Max((ws1.range("B3:B", 100)=A3)*'ws2.range("I3:I",100)))"

here worksheets are defined as ws1 and ws2.
Sorry but your code is not executing, please help is there any other problem in code
Reply With Quote
#4
Old 04-04-2011
Blazej's Avatar
Blazej Blazej is offline
Member

Join Date: May 2008
Posts: 863
Re: I need code for last purchase or transaction Date for my inventory system
Dude still you haven’t uploaded any sample book so it’s not easy to understand what is the problem and where is the code mistake, as said before please upload your sample book so it will more convenient for me and for others to understand your problem easily. And by looking at your code some things are not clear like what is “.MAX”, according to me it should be
Code:

worksheetfunction.Max or it can be like this with worksheetfunction
l = .SumProduct(.Max((ws1.range("B3:B", 100)=A3)*'ws2.range("I3:I",100)))"

end with

and one more thing is not clear why there is ‘ before ws2.range ?
Reply With Quote
#5
Old 05-04-2011
Max-well} Max-well} is offline
Member

Join Date: Mar 2011
Posts: 57
Re: I need code for last purchase or transaction Date for my inventory system
Ok buddy here’s the Macro code please have a look
Code:

Sub report()
Dim ws1 As Worksheet
Dim wsR As Worksheet
Dim LastRow, j, t As Long
Dim i As Integer

On Error Resume Next
Set ws1 = Worksheets("Trns")
Set wsR = Worksheets("Report")
wsR.Select

LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row

ws1.Range("A2:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=wsR.Range("A1"), Unique:=True

j = wsR.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To j

With WorksheetFunction
t = Cells(i, 1).Value
Cells(i, 2).Value = .CountIf(ws1.Range("A:A"), Cells(i, 1))
Cells(i, 3).Value = .SumIf(ws1.Range("A:A"), Cells(i, 1), ws1.Range("C:C"))
Cells(i, 4).Value = .SumProduct(.Max((ws1.Range("a3:a100") = t) * ws1.Range("D3:d100")))

End With
Next i

End Sub

Hope many things will be clear to you. And I sent you sample book. And there is no ‘ before ws2.range it’s just a typing mistake.