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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE