Previous Topic Next Topic

Last Transaction Date

Closed
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 - 10 Mar 2011 à 23:53
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 18 Mar 2011 à 09:05
Hello,

I have some inventory data. in which column are as

item_code purchase_date quantity amount

in another sheet i have item code and i need last purchase date (which are not in order in privious sheet), no. of purchase, and total purchase.
is there any code to determine this,

Please help me out


Related:

4 responses

Réponse 1 / 4
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
11 Mar 2011 à 05:25
max function should give you the last date
Réponse 2 / 4
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
11 Mar 2011 à 07:27
hi Riz,

I have to use sumproduct and max function in macro as 


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

ws1 & ws2 are define as worksheet

but not code is not executing
tell me is there any problem in code.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
11 Mar 2011 à 09:50
well hard to tell you like that. May be if you could post a sample book and give more information about error some one would be able to help you better.

how ever just looking at your code, couple of things jump right now

what is ".MAX", i think it should be like worksheetfunction.Max

or may be like this

with worksheetfunction

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

end with

other thing is this ' before ws2.range. Why you need it ?
Réponse 3 / 4
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
13 Mar 2011 à 23:54
there is no ' before ws2.range. its typing error, the code is 

with worksheetfunction   
Range("K3").Value = .sumproduct(.Max((ws1.Range("B3:B100") = Range("A3"))_
 * ws1.Range("I3:I100")))  
end with


The following error shows

run time error '13'
type mismatch
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Mar 2011 à 09:32
Again hard for me to tell you without looking at the book and the data.
Réponse 4 / 4
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
16 Mar 2011 à 03:01
hi,

https://authentification.site/files/27426011/test.xlsm

the uploaded file is a part of my sheet and the macro code for this part is 
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Mar 2011 à 08:33
No file there
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
17 Mar 2011 à 23:39
https://authentification.site/files/27464719/test.xlsm
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Mar 2011 à 09:05
ran this this code on your sample book. I dont get any error . Tried with the code in the book and the code here, both gives me no issue. Am i missing some thing here ?