How can we use Multiplication in Sumproduct

[GirishDhruva]

Hi Everyone,
Here i am trying to multiply a particular columns using SUMPRODUCT but its giving me the output as '0'

Can i know why is this happening???

 With Worksheets("Pivot")    finalcolumn = 1 + .Cells(2, 1).End(xlToRight).Column
    .Cells(2, finalcolumn).Select
    Nxt = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    Hdr = .Range(.Cells(1, 1), .Cells(1, Nxt - 1)).Address(, , xlR1C1)
    Range(.Cells(2, Nxt), .Cells(.Rows.Count, Nxt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = _
        "=SUMPRODUCT(((" & Hdr & "=""Sum of ABC PM"")/30)*(" & Hdr & "=""EA""),(rc1:rc[-1]))"
End With

Any Suggestions/Solutions for the above code

Regards,
Dhruv

 

[steve the fish]

We cant see your worksheet but that said that formula can only ever produce 0. Its not possible that cells in a range are equal to "Sum of ABC PM" and also equal to "EA".

 

[GirishDhruva]

We cant see your worksheet but that said that formula can only ever produce 0. Its not possible that cells in a range are equal to "Sum of ABC PM" and also equal to "EA".

Hi @steve the fish
Here below i have shared my workbook
https://app.box.com/s/p36n4dk08znk2g57iftswh5zz2q4k1pu

Regards,
Dhruv

 

[steve the fish]

Hi. Just explain the problem here.

 

[GirishDhruva]

Hi,
Here i trying to find the columns by column header and from those columns i need to apply the below formula in last empty column
This formula was done manually but i am trying this in VBA

"=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0)"


Below is the code which i tried but i couldn't perform multiplication.

With Worksheets("Pivot")
    finalcolumn = 1 + .Cells(2, 1).End(xlToRight).Column
    .Cells(2, finalcolumn).Select
    Nxt = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    Hdr = .Range(.Cells(1, 1), .Cells(1, Nxt - 1)).Address(, , xlR1C1)
    Range(.Cells(2, Nxt), .Cells(.Rows.Count, Nxt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = _
        "=SUMPRODUCT(((" & Hdr & "=""Sum of ABC PM"")/30)*(" & Hdr & "=""EA""),(rc1:rc[-1]))"
End With

Regards,
Dhruv

 

[steve the fish]

So in your formula you have C2,D2,F2,G2 and H2 yet in your code just Sum of ABC PM and EA. How can we know which ones are fixed and which ones are the headers?

 

[GirishDhruva]

My first row contains headers and from that headers i need to find the value using the formula , but each and every time my columns might be varying so i need to find the columns through the column header and apply that formula

=SUMPRODUCT(((" & Hdr & "=""Sum of ABC PM"")/31)*(" & Hdr & "=""EA""))+(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPL""))-(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPLR"")),(rc1:rc[-1]))

 

[steve the fish]

=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0)

What words are in C1, D1, E1, F1, G1 and H1? I presume those are the header cells yes? Why are we using sumproduct here?

 

[GirishDhruva]

=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0) ,This formula can be applied in excel sheet directly but i am trying that in vba so

With Worksheets("Pivot")
    finalcolumn = 1 + .Cells(2, 1).End(xlToRight).Column
    .Cells(2, finalcolumn).Select
    Nxt = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    Hdr = .Range(.Cells(1, 1), .Cells(1, Nxt - 1)).Address(, , xlR1C1)
    Range(.Cells(2, Nxt), .Cells(.Rows.Count, Nxt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = _
        "=SUMPRODUCT((((" & Hdr & "=""Sum of ABC PM"")/31)*(" & Hdr & "=""EA""))+(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPL""))-(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPLR"")),(rc1:rc[-1]))"
End With

Instead of using the formula in excel sheet i am trying to perform that using vba by the above code but it's throwing me '#DIV/0!' error

My main goal is to search for particular columns using column headers and perform the above formula in VBA

 

[steve the fish]

Sure i know. Did you even read then questions put to you?