I'm newbie in vba macro.
Currently i facing a problem using the sumproduct in my macro.(i'm using excel 2003)
My logic is like that is sheet1(label no) = sheet 2 (label no) & sheet 1 (amount) <>sheet 2(amount) then copy sheet 2 data into sheet 3
. My coding is below but i still can not copy them into sheet3. Anyone have any idea.
.Range("A1:L1").Value = Range("A1:L1").Value
lastrow = Worksheets("CMPK").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
.Range("Q11") = ""
Application.StatusBar = "Actioning 1 of 3 Row: " & i
cont_no = Cells(i, 2)
amount = Cells(i, 9)
'If CMPK Label No = AS AT 2012 & CMPK AMOUNT <> AS AT 2012 AMOUNT
myval = Evaluate("SUMPRODUCT(CMPK!A:A=""" & cont_no & """)*(CMPK!C:C <> """ & amount & """)")
.Range("Q11") = myval
If .Range("Q11") = 1 Then
Cells(i, 1).Resize(1, 12).Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next i
Bookmarks