On Aug 29, 3:36 pm, Chandra Shekar <chandrashekarb....@gmail.com>
wrote:
> Hello,
>
> Could you please let me know whats the error in using sumproduct function in
> below code. I am getting error number 2015
>
> Is there any other way to use sumproduct function using vba with multiple
> condtions.
>
> Thanks in advance.
>
> Sub ABNAMROAdd()
> Dim compnm As String
> lastcl = ThisWorkbook.Worksheets("ABN AMRO").Cells(4,
> Columns.Count).End(xlToLeft).Column
> 'WeekNum = Weeknumber(Date)
> 'WeekNum = CurWk
> x = ThisWorkbook.Name
> y = ThisWorkbook.Worksheets(12).Name
> Z = "'[" & x & "]" & y & "'!"
>
> lastrow = ThisWorkbook.Worksheets("AR").Cells(Rows.Count, 1).End(xlUp).Row
> rng1 = Z & "A2:A" & lastrow
> rng2 = Z & "C2:C" & lastrow
> rng3 = Z & "D2:D" & lastrow
> rng4 = Z & "E2:E" & lastrow
> rng5 = Z & "H2:H" & lastrow
> a = "SUMPRODUCT(--(" & rng1 & "=""" & "BEL" & """),--(" & rng2 & "=""" &
> "AAO" & """),--(" & rng4 & """))"
> cnt = Application.Evaluate(a)
>
> End Sub
Can't speak for the logic of what you are trying to do, but your
SUMPRODUCT has an extra " mark in it which is what is causing the
error. Try:
a = "SUMPRODUCT(--(" & rng1 & "=""" & "BEL" & """),--(" & rng2 & "="""
& "AAO" & """),--(" & rng4 & "))"
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
