Sub MacroAll()
Dim Main As Worksheet
Set Main = Sheets("Main")
Set ShD = Sheets("NewData")
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
For Each cl In Range(Main.Range("B3"), Main.Range("D3"))
mySum = 0
Application.StatusBar = "Processing month " & Main.Cells(cl.Row, 1).Value & " for year " & Main.Cells(2, cl.Column).Value
For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
If ShD.Cells(c.Row, intYearCol).Value = (Main.Cells(2, cl.Column).Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A" & cl.Row).Value2 * 1) _
And ShD.Cells(c.Row, intMonthCol).Value <> 111 And _
ShD.Cells(c.Row, intProductCol).Value Like "[5-7]*" Then
mySum = mySum + ShD.Cells(c.Row, intAmountCol).Value
End If
Next
cl.Value = mySum
Next
Application.StatusBar = False
End Sub
Sub MacroAll()
Dim Main As Worksheet
Set Main = Sheets("Main")
Set ShD = Sheets("NewData")
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
For Each cl In Range(Main.Range("B3"), Main.Range("D3"))
mySum = 0
Application.StatusBar = "Processing month " & Main.Cells(cl.Row, 1).Value & " for year " & Main.Cells(2, cl.Column).Value
For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
If ShD.Cells(c.Row, intYearCol).Value = (Main.Cells(2, cl.Column).Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A" & cl.Row).Value2 * 1) _
And ShD.Cells(c.Row, intMonthCol).Value <> 111 And _
ShD.Cells(c.Row, intProductCol).Value Like "[5-7]*" Then
mySum = mySum + ShD.Cells(c.Row, intAmountCol).Value
End If
Next
cl.Value = mySum
Next
Application.StatusBar = False
End Sub
WE.xlsb
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.