Avatar of Flora Edwards
Flora Edwards
Flag for Sweden asked on

VBA modification from earlier solution by Rgonzo1971

I had this question after viewing Simple Macro but is a big challenge. any Expert can solve this please?.


Rgonzo1971 helped me alot. thanks to him.

he helped me with the code below.

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

Open in new window


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

2017-11-28-14_18_06-WE.xlsb---Excel.png
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 = False

End Sub

Open in new window

WE.xlsb
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Flora Edwards

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Bembi

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Flora Edwards

ASKER
Thank you very much
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy