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 = FalseEnd Sub
I wanted to modify the code, so that it takes the years from the upper 3 rows on the columns B C D
i modified the code like this, but it does not work and i do not know how to include the other 2 upper columns highlighted in yellow into the code
Attached is the workbook.
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 = FalseEnd Sub