This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
Sub macro2011_Month_2()
'for 2011 month 2
Dim Main As Worksheet
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set Main = Sheets("Main")
Set ShD = Sheets("Data")
For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
If ShD.Cells(c.Row, intYearCol).Value = (Main.Range("B2").Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A3").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
Main.Range("B3") = mySum
End Sub
Sub Macro2012_Month_2()
' for 2012 month 2
Dim Main As Worksheet
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set Main = Sheets("Main")
Set ShD = Sheets("Data")
For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
If ShD.Cells(c.Row, intYearCol).Value = (Main.Range("C2").Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A3").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
Main.Range("C3") = mySum
End Sub
Sub macro2011_Month_2()
'for 2011 month 2
Dim Main As Worksheet
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set Main = Sheets("Main")
Set ShD = Sheets("Data")
For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
If ShD.Cells(c.Row, intYearCol).Value = (Main.Range("B2").Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = 3_
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
Main.Range("B3") = mySum
End Sub
Sub CombinedResult()
Dim Ws As Worksheet, DataSh As Worksheet
Dim LRow As Long, RowIndex As Long, ColIndex As Long, LR As Long, LC As Long
Dim RngYear As Range, RngMonth As Range, ProdRng As Range, SumRng As Range
Dim ProdCrit1 As String, ProdCrit2 As String, ProdCrit3 As String
Dim FinalResult As Variant
Set Ws = Worksheets("Main")
Set DataSh = Worksheets("Data")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
LC = Ws.Cells(2, Columns.Count).End(xlToLeft).Column
LRow = DataSh.Range("A" & Rows.Count).End(xlUp).Row
Set RngYear = DataSh.Range("A2:A" & LRow)
Set RngMonth = DataSh.Range("B2:B" & LRow)
Set ProdRng = DataSh.Range("C2:C" & LRow)
Set SumRng = DataSh.Range("D2:D" & LRow)
ProdCrit1 = "5*"
ProdCrit2 = "6*"
ProdCrit3 = "7*"
Application.ScreenUpdating = False
For RowIndex = 3 To LR
For ColIndex = 2 To LC
FinalResult = Application.WorksheetFunction.SumIfs(SumRng, ProdRng, ProdCrit1, RngMonth, Ws.Cells(RowIndex, "A").Value, RngYear, Ws.Cells(2, ColIndex).Value) + Application.WorksheetFunction.SumIfs(SumRng, ProdRng, ProdCrit2, RngMonth, Ws.Cells(RowIndex, "A").Value, RngYear, Ws.Cells(2, ColIndex).Value) + Application.WorksheetFunction.SumIfs(SumRng, ProdRng, ProdCrit3, RngMonth, Ws.Cells(RowIndex, "A").Value, RngYear, Ws.Cells(2, ColIndex).Value)
Ws.Cells(RowIndex, ColIndex).Value = FinalResult
Next ColIndex
Next RowIndex
Ws.Activate
Application.ScreenUpdating = True
End Sub
Flora_MultipleSums_v3.xlsb
Sub macroAll()
Dim Main As Worksheet
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set Main = Sheets("Main")
Set ShD = Sheets("Data")
For Each cl In Range(Main.Range("B3"), Main.Range("C10"))
mySum = 0
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
End Sub
Regards
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Try below:
Open in new window
Flora_MultipleSums.xlsb