Macro for summation of few(4) columns based on selection criteria of two columns

[nitz00757]

Hi,
I am new to the excel macro world and my requirement is to get the summation of few(4) columns based on selection criteria of two columns. I have attached the dummy excel sheet. I want to make a generic macro which when run give me the desired output as per the values in excel sheet. Currently m using below code.
Please run the macro in the attached sheet you will come to know my requirement.

Sub test()
Dim v_count As Integer
Dim F_count As Integer
Dim U_count As Integer
Dim AA_count As Integer
Dim AG_count As Integer

Worksheets("Sort").Range("D3") = Worksheets("DM Piping").Range("D1").Value
Worksheets("Sort").Range("E3") = Worksheets("DM Piping").Range("H1").Value
Worksheets("Sort").Range("F3") = Worksheets("DM Piping").Range("I1").Value
Worksheets("Sort").Range("G3") = Worksheets("DM Piping").Range("O1").Value
Worksheets("Sort").Range("H3") = Worksheets("DM Piping").Range("U1").Value
Worksheets("Sort").Range("I3") = Worksheets("DM Piping").Range("AA1").Value
Worksheets("Sort").Range("J3") = Worksheets("DM Piping").Range("AG1").Value

Worksheets("Sort").Range("D3:J3").Select
Selection.Font.Bold = True

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

'IF YOU WANT TO ADD ANY OTHER "MOC & SIZE" THEN JUST COPY PASTE THE BELOW "FOR LOOP"
'MAKE SURE FOR ANY NEWELY ADDED "FOR LOOP" SHOULD HAVE NEW VARIABLE NAME OTHER THAN(i,j,k,m)
'ALSO YOU HAVE TO CHANGE THE PRINING CELL VALUES, WHICH ARE D4,E4,F4,G4,H4,I4,J4 IN THE BELOW CASE

'START OF "FOR LOOP"
For i = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & i).Value = "MS" And Worksheets("DM Piping").Range("H" & i).Value = "50" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & i).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & i).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & i).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & i).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & i).Value

End If

Next
Worksheets("Sort").Range("D4") = "MS"
Worksheets("Sort").Range("E4") = "50"
Worksheets("Sort").Range("F4") = v_count
Worksheets("Sort").Range("G4") = F_count
Worksheets("Sort").Range("H4") = U_count
Worksheets("Sort").Range("I4") = AA_count
Worksheets("Sort").Range("J4") = AG_count

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0
'END OF "FOR LOOP"

For j = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & j).Value = "MS" And Worksheets("DM Piping").Range("H" & j).Value = "250" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & j).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & j).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & j).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & j).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & j).Value

End If

Next
Worksheets("Sort").Range("D5") = "MS"
Worksheets("Sort").Range("E5") = "250"
Worksheets("Sort").Range("F5") = v_count
Worksheets("Sort").Range("G5") = F_count
Worksheets("Sort").Range("H5") = U_count
Worksheets("Sort").Range("I5") = AA_count
Worksheets("Sort").Range("J5") = AG_count


v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

For k = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & k).Value = "MS" And Worksheets("DM Piping").Range("H" & k).Value = "300" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & k).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & k).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & k).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & k).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & k).Value

End If

Next
Worksheets("Sort").Range("D6") = "MS"
Worksheets("Sort").Range("E6") = "300"
Worksheets("Sort").Range("F6") = v_count
Worksheets("Sort").Range("G6") = F_count
Worksheets("Sort").Range("H6") = U_count
Worksheets("Sort").Range("I6") = AA_count
Worksheets("Sort").Range("J6") = AG_count

v_count = 0
F_count = 0
U_count = 0
AA_count = 0
AG_count = 0

For m = 1 To Sheet1.UsedRange.Rows.Count

If Worksheets("DM Piping").Range("D" & m).Value = "MS" And Worksheets("DM Piping").Range("H" & m).Value = "350" Then
v_count = v_count + Worksheets("DM Piping").Range("I" & m).Value
F_count = F_count + Worksheets("DM Piping").Range("O" & m).Value
U_count = U_count + Worksheets("DM Piping").Range("U" & m).Value
AA_count = AA_count + Worksheets("DM Piping").Range("AA" & m).Value
AG_count = AG_count + Worksheets("DM Piping").Range("AG" & m).Value

End If

Next
Worksheets("Sort").Range("D7") = "MS"
Worksheets("Sort").Range("E7") = "350"
Worksheets("Sort").Range("F7") = v_count
Worksheets("Sort").Range("G7") = F_count
Worksheets("Sort").Range("H7") = U_count
Worksheets("Sort").Range("I7") = AA_count
Worksheets("Sort").Range("J7") = AG_count


Worksheets("Sort").Range("D3:J7").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With


End Sub

[Mordred]

Hello nitz00757 and welcome to the forum.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here