Excel VBA sumproduct quotation marks

    Hi all,


    Slight problem, i have the below sumproduct within VBA


    Code
    .Cells(boynefin, col).Formula = "=(SUMPRODUCT(R[-9]C*R5C+R[-8]C*R5C+R[-7]C*R5C+R[-6]C*R5C+R[-5]C*R5C+R[-4]C*R5C+R[-3]C*R5C)*INDICIES!R4C17+SUMPRODUCT(R[-9]C[-23]:R[-3]C[-23],REPORT2!R[-9]C:R[-3]C)*INDICIES!R2C17+((R5C+R[-2]C[-24])*R[-2]C)-SUMPRODUCT((R[-9]C1=""" & plantboyne & """),DATABASE!" & APPC_PRICE_ADJ_COL & "2:" & APPC_PRICE_ADJ_COL & lastrow & "))"


    but when this value gets input into the cell the macro puts ' around the last part of the formula, rather than making it a range. Can someone let me know what i'm doing wrong? Thanks in advance.


    =(SUMPRODUCT(AA28*AA$5+AA29*AA$5+AA30*AA$5+AA31*AA$5+AA32*AA$5+AA33*AA$5+AA34*AA$5)*INDICIES!$Q$4+SUMPRODUCT(D28:D34,REPORT2!AA28:AA34)*INDICIES!$Q$2+((AA$5+C35)*AA35)-SUMPRODUCT(($A28="Boyne"),DATABASE!'BF2':'BF18572'))


    Ryan

    Re: Excel VBA SUMPRODUCT QUOTATION MARKS


    Hi,


    I think the problem is probably caused by the mixed references that you are using - part R1C1 and past A1. If you can re-write the initial part of the code to use A1 also then I suspect that the problem will go away.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login