formula to be converted in vba

[RAM72]

Hi All

Need help to convert formula in vba still to last row data row.

That is data is dynamic it may have 1000 records and summay records 125
or 500 records , summary 150 records

1 On Summary Report Field header Qty_1 and Amount which sumproduct formula has been used to consolidate the report .
Using sumproduct need for macro to work on excel 2003 also.

The formula are already inserted in their respective field headers.

Other button macros already adjusted and working fine .

2. On working sheet header Itemised need to be converted in vba formula are already inserted in cells,

The recorded macros are of same colour of their respective sheets .

See attached file

 

[Hui]

I think this is what you want for the Summary Report

Sub TEST_SUMPRODUCT()
'
' TEST_SUMPRODUCT Macro
'

  Dim lrw As Integer, lrs As Integer

  Sheets("Summary Report ").Select

  lrw = Sheets("Workings").Range("A" & Rows.Count).End(xlUp).Row
  lrs = Range("A" & Rows.Count).End(xlUp).Row

  Range("D2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings!R2C11:R" + CStr(lrw) + "C11,--(Workings!R2C8:R" + CStr(lrw) + "C8='Summary Report '!RC1),--(Workings!R2C9:R" + CStr(lrw) + "C9='Summary Report '!RC2),--(Workings!R2C10:R" + CStr(lrw) + "C10='Summary Report '!RC3))"
  Range("E2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings!R2C14:R" + CStr(lrw) + "C14,--(Workings!R2C8:R" + CStr(lrw) + "C8='Summary Report '!RC1),--(Workings!R2C9:R" + CStr(lrw) + "C9='Summary Report '!RC2),--(Workings!R2C10:R" + CStr(lrw) + "C10='Summary Report '!RC3))"
  Range("D2:E2").Select
  Selection.AutoFill Destination:=Range("D2:E" + CStr(lrs))
  Range("D2:E" + CStr(lrs + 1)).Select
End Sub

This code must be located in the Summary report code module in the VBE

 

[RAM72]

Thanks Hui code working

But facing an another issue in column Itemised , the macro is showing 0 everywhere, checked my formula working perfectly

See attached sheet workings macro higlighted in yellow

=INDEX('Summary Report '!$G$2:$G$124,MATCH(H2&I2&J2,'Summary Report '!$A$2:$A$124&'Summary Report '!$B$2:$B$124&'Summary Report '!$C$2:$C$124,0))

Sub testitemised()
    Dim LRS As Long
    Dim LRW As Long
    With Worksheets("Summary Report ")
        LRS = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    With Worksheets("Workings")
        LRW = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("O2:O" & LRW).FormulaArray = _
            "=INDEX('Summary Report '!R2C8:R" & LRS & "C8,MATCH(RC[-7]&RC[-6]&RC[-5],'Summary Report '!R2C1:R" & LRS & "C1&'Summary Report '!R2C2:R" & LRS & "C2&'Summary Report '!R2C3:R" & LRS & "C3,0))"
    End With
End Sub

 

[Deepak]

RAM72

Hope! you have got working it now!!

http://chandoo.org/forum/threads/macro-index-and-match-not-working.28084/

 

[RAM72]

RAM72

Hope! you have got working it now!!

http://chandoo.org/forum/threads/macro-index-and-match-not-working.28084/

Yes it ok thanks