Hi all,
How do I get the top 5 countries in different cell with a macro button?
I have the following data for example:
First column is country list, second column is inout date
china Date
china
china
USA
USA
Hong Kong
Hong Kong
USA
Germany
Germany
UK
Germany
UK
Singapore
Singapore
Canada
UK
Canada
Italy
.......
I have created an formula in each cell but need Ctrl+shift+enter to work:
=INDEX('Accepted Cases'!$F$7:$F$65536,MODE(IF(('Accepted Cases'!$F$7:$F$65536<>"")*ISNA(MATCH('Accepted Cases'!$F$7:$F$65536,$B$4:$B4,0))*(('Accepted Cases'!$P$7:$P$65536)>='Summary Country'!$E4)*(('Accepted Cases'!$P$7:$P$65536)<=(DATE(2014,2,28))),MATCH('Accepted Cases'!$F$7:$F$65536,'Accepted Cases'!$F$7:$F$65536,0))))
How can I show the top 5 countries in 5 different cell by VBA?
I got the error message:
"unable to set the formulaarray property of the range class"
this is what i input:
Sub top5()
Application.Calculation = xlCalculationManual
Range("B5").FormulaArray = "=INDEX('Accepted Cases'!$F$7:$F$65536,MODE(IF(('Accepted Cases'!$F$7:$F$65536<>"")*ISNA(MATCH('Accepted Cases'!$F$7:$F$65536,$B$4:$B4,0))*(('Accepted Cases'!$P$7:$P$65536)>='Summary Country'!$E4)*(('Accepted Cases'!$P$7:$P$65536)<=(DATE(2014,2,28))),MATCH('Accepted Cases'!$F$7:$F$65536,'Accepted Cases'!$F$7:$F$65536,0))))"
End Sub
Thank you
Jen
Bookmarks