Hi All,
I just joined XLguru and this is my first post.
I have one issue for which i would need your help/feedback.
This is my issue:
I have a master data in "Sheet1" containing info about Month, Region, Activity, Nbr of cartons and cycle time (in decimal),...
MonthRegionActivityCTCarton2GCIR23271GCIR24291SAIR11102SAIR23282SAIR24231SAIR24232SAMFE3732SAMFE371
With a VBA program, excel copies the data in "Sheet2" and proceed to a sorting + inserting an empty row to separate the data per month, region, activity, as follow:
Code as below:
And i obtain the following result:
MonthRegionActivityCTCarton1GCIR24.229?292GCIR23.127?271SAIR11101SAIR24.223?332SAIR23.1282SAIR24.223?512SAMFE3732SAMFE371?4
The above data are in the following column:
Month = Column B
Region = Column C
Activity = Column D
CT = Column J
Carton = Column M
As you can see the above data, after sorting, looks like blocks of data.
To calculate the SUM of my nbr of cartons and to insert the result in the empty row, I am using the following code:
Now I would need to calculate for each block of data the SumProduct of the CT(col10) versus the Carton(col13): =SUMPRODUCT(CT(col10)*Carton(col13))/TotCarton(col13)
And insert the result in the empty row in the corresponding column (see "?")
This knowing that the above data is dynamic, so i cannot determine a fix range like ("C2:C7"), because the lengh of those ranges will change every month.
Please let me know if you can help me coding this SUMPRODUCT portion for my case.
Thanks in advance for your help,
Lomic6
I just joined XLguru and this is my first post.
I have one issue for which i would need your help/feedback.
This is my issue:
I have a master data in "Sheet1" containing info about Month, Region, Activity, Nbr of cartons and cycle time (in decimal),...
MonthRegionActivityCTCarton2GCIR23271GCIR24291SAIR11102SAIR23282SAIR24231SAIR24232SAMFE3732SAMFE371
With a VBA program, excel copies the data in "Sheet2" and proceed to a sorting + inserting an empty row to separate the data per month, region, activity, as follow:
Code as below:
Code:
Sub WorkSheetSort1()
Dim Rw As Range
Dim Column As Long
Application.ScreenUpdating = False
Worksheets("Master").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, cells(1)).Select
For Each Rw In Selection.Rows
Column = Rw.Row
If Rw.cells(1, 1).Value <> " " Then
Rw.Copy Destination:=Worksheets("Region").cells(Column, 1).EntireRow
End If
Next Rw
Worksheets("Master").Select
Range("A1").Select
Worksheets("Region").Select
Range("A:U").Sort Key1:=Range("C2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlYes, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Call myMonth
Call InsertRows1
Call mySubT1
Call myAutoFit
End Sub
And i obtain the following result:
MonthRegionActivityCTCarton1GCIR24.229?292GCIR23.127?271SAIR11101SAIR24.223?332SAIR23.1282SAIR24.223?512SAMFE3732SAMFE371?4
The above data are in the following column:
Month = Column B
Region = Column C
Activity = Column D
CT = Column J
Carton = Column M
As you can see the above data, after sorting, looks like blocks of data.
To calculate the SUM of my nbr of cartons and to insert the result in the empty row, I am using the following code:
Code:
Sub mySubT1()
'Do Subtotal SUM
For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
Rows(aArea.Row + aArea.Rows.Count).Font.ColorIndex = 5
Rows(aArea.Row + aArea.Rows.Count).Font.Bold = True
cells(aArea.Row + aArea.Rows.Count, 13).Formula = "=SUM(" & Range(cells(aArea.Row, 13), cells(aArea.Row + aArea.Rows.Count - 1, 13)).Address & ")"
cells(aArea.Row + aArea.Rows.Count, 14).Formula = "=SUM(" & Range(cells(aArea.Row, 14), cells(aArea.Row + aArea.Rows.Count - 1, 14)).Address & ")"
cells(aArea.Row + aArea.Rows.Count, 19).Formula = "=SUM(" & Range(cells(aArea.Row, 19), cells(aArea.Row + aArea.Rows.Count - 1, 19)).Address & ")"
cells(aArea.Row + aArea.Rows.Count, 20).Formula = "=SUM(" & Range(cells(aArea.Row, 20), cells(aArea.Row + aArea.Rows.Count - 1, 20)).Address & ")"
Next aArea
End Sub
Now I would need to calculate for each block of data the SumProduct of the CT(col10) versus the Carton(col13): =SUMPRODUCT(CT(col10)*Carton(col13))/TotCarton(col13)
And insert the result in the empty row in the corresponding column (see "?")
This knowing that the above data is dynamic, so i cannot determine a fix range like ("C2:C7"), because the lengh of those ranges will change every month.
Please let me know if you can help me coding this SUMPRODUCT portion for my case.
Thanks in advance for your help,
Lomic6