• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to adjust Sumifs formula in vba code on another worksheet

Status
Not open for further replies.

RAM72

Member
Hi All

I have a vba code for sumifs for data and summary sheet in yellow tabs, Macro found on summary sheet (yellow)

Need to adjust macro for red tabs data_1 and summary report_1. Columns involved in data_1 are columns H,I,J,K,Min blue.

An advanced filter for unique records is made through macro with their respective headers in summary report _1

Can anyone help to adjust code for summary report_1 columns involved are D and E in red as attached

Code:
Sub RAM()
Dim LastR As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("data")
Set ws2 = Sheets("summary")


LastR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    With ws2
        .Range("A1:E1").Value = ws1.Range("A1:E1").Value
        .Range(.Range("A2"), .Cells(LastR, 4)).Value = ws1.Range(ws1.Range("A2"), ws1.Cells(LastR, 4)).Value
        .Range(.Range("A2"), .Cells(LastR, 4)).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
       
        LastR = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range(.Range("E2"), .Cells(LastR, 5)).Value = _
        "=SUMIFS(data!C,data!C[-4],RC[-4],data!C[-3],RC[-3],data!C[-2],RC[-2],data!C[-1],RC[-1])"
        .Range(.Range("E2"), .Cells(LastR, 5)).Value = .Range(.Range("E2"), .Cells(LastR, 5)).Value
    End With
End Sub
 

Attachments

Nebu

Excel Ninja
Hi:

I have created named ranges for the macro, right click on the Summary Report_1 tab select view code and paste the following code there.

Code:
Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Application.ScreenUpdating = True
End Sub
Thanks
 

Attachments

RAM72

Member
Hi:

I have created named ranges for the macro, right click on the Summary Report_1 tab select view code and paste the following code there.

Code:
Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Application.ScreenUpdating = True
End Sub
Thanks
Hi Nebu

Thanks works well but does sumifs only for column D only.

Could you adjust code so that macro works simultaneously for column D and E
 

Nebu

Excel Ninja
Hi:

We expect you to put little bit of effort from your end , after all this is your report. You are getting solution here for free, it does not mean that we should do all the work for you. Replicating the same logic for column E was not a big task. If you are not ready to put in a bit of effort from your end to understand the codes/formulas do not expect solutions from our end. I can guarantee you that all the ninjas here are not born with excel skills, we had put our bit of effort to learn the bit of excel we know. Anyhow , here is the code including column E.


Code:
Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Me.Range("E2").Formula = "=SUMIFS(montant,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("E2:E" & i).FillDown
Application.ScreenUpdating = True
End Sub
Thanks
 

RAM72

Member
Hi:

We expect you to put little bit of effort from your end , after all this is your report. You are getting solution here for free, it does not mean that we should do all the work for you. Replicating the same logic for column E was not a big task. If you are not ready to put in a bit of effort from your end to understand the codes/formulas do not expect solutions from our end. I can guarantee you that all the ninjas here are not born with excel skills, we had put our bit of effort to learn the bit of excel we know. Anyhow , here is the code including column E.


Code:
Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Me.Range("E2").Formula = "=SUMIFS(montant,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("E2:E" & i).FillDown
Application.ScreenUpdating = True
End Sub
Thanks
Hi

Do agree, thanks for your kind help
 
Status
Not open for further replies.
Top