Results 1 to 4 of 4

vba SumProduct Error

Threaded View

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    48

    vba SumProduct Error

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1