Previous Topic Next Topic

Last Transaction Date

Closed
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 10, 2011 at 11:53 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 18, 2011 at 09:05 AM
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

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 11, 2011 at 05:25 AM
max function should give you the last date
0
Answer 2 / 4
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 11, 2011 at 07:27 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 11, 2011 at 09:50 AM
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 ?
0
Answer 3 / 4
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 13, 2011 at 11:54 PM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 14, 2011 at 09:32 AM
Again hard for me to tell you without looking at the book and the data.
0
Answer 4 / 4
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 16, 2011 at 03:01 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 08:33 AM
No file there
0
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 17, 2011 at 11:39 PM
https://authentification.site/files/27464719/test.xlsm
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 18, 2011 at 09:05 AM
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 ?
0