You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Concurrent SUM & SUMPRODUCT calculation in a dynamic range.
- Thread starter Lomic6
- Start date
Hi Roger,
After our last exchange of message, I have one question regarding the generation of the charts:
The following coding is for a 1 dimension criteria.
, but is it possible to do a chart with VBA using a 2 dimensions or 3 dimensions criteria, or even more ? Or are we limited for that ?
I tried coding it as follow:
Etc, etc... same for Europe, Asia, and Africa area.
But it is not working, no value appearing in my chart.
Should I replicate the coding from "For Each c In wsSRA.Range.." till "Typ2..." for the second criteria ? Or should I create a d dimension ?
Thanks in advance for your good advice,
Regards,
Lomic6
After our last exchange of message, I have one question regarding the generation of the charts:
The following coding is for a 1 dimension criteria.
Code:
Sub myChartDataReg()
Dim c As Range, typ As String, tr As Long, month As Long
Dim output(6)
Dim wsSR As Worksheet, wsCR As Worksheet
Set wsSR = Sheets("Reg")
Set wsCR = Sheets("ChartReg")
Application.ScreenUpdating = False
wsSR.Activate
wsSR.UsedRange.AutoFilter Field:=35, Criteria1:="<>"
For Each c In wsSR.Range("AQ:AQ").SpecialCells(12)
tr = c.Row
If tr <> 1 Then
output(1) = wsSR.Cells(tr, "P").Value
output(2) = wsSR.Cells(tr, "Q").Value
output(3) = wsSR.Cells(tr, "R").Value
output(4) = wsSR.Cells(tr, "S").Value
output(5) = wsSR.Cells(tr, "T").Value
output(6) = wsSR.Cells(tr, "U").Value
month = wsSR.Cells(tr, "AH").Value
typ = wsSR.Cells(tr, "AI").Value
If typ = "America" Then
wsCR.Range("D45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Europe" Then
wsCR.Range("D97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Asia" Then
wsCR.Range("D149").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Africa" Then
wsCR.Range("D201").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
Else
GoTo exit_Sub
End If
output(1) = wsSR.Cells(tr, "V").Value
output(2) = wsSR.Cells(tr, "W").Value
output(3) = wsSR.Cells(tr, "X").Value
output(4) = wsSR.Cells(tr, "AB").Value
output(5) = wsSR.Cells(tr, "Z").Value
output(6) = wsSR.Cells(tr, "AC").Value
If typ = "America" Then
wsCR.Range("X45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Europe" Then
wsCR.Range("X97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Asia" Then
wsCR.Range("X149").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "Africa" Then
wsCR.Range("X201").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
Else
GoTo exit_Sub
End If
End If
Next
Selection.NumberFormat = "0.0"
exit_Sub:
wsSR.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub, but is it possible to do a chart with VBA using a 2 dimensions or 3 dimensions criteria, or even more ? Or are we limited for that ?
I tried coding it as follow:
Code:
Dim c As Range, d As Range, typ1 As String, typ2 As String, tr As Long, month As Long
Dim output(6)
Dim wsSRA As Worksheet, wsCRA As Worksheet
Set wsSRA = Sheets("RegAct")
Set wsCRA = Sheets("ChartRegAct")
Application.ScreenUpdating = False
wsSRA.Activate
wsSRA.UsedRange.AutoFilter Field:=35, Criteria1:="<>"
wsSRA.UsedRange.AutoFilter Field:=36, Criteria2:="<>"
For Each c In wsSRA.Range("AL:AL").SpecialCells(12)
tr = c.Row
If tr <> 1 Then
output(1) = wsSRA.Cells(tr, "P").Value
output(2) = wsSRA.Cells(tr, "Q").Value
output(3) = wsSRA.Cells(tr, "R").Value
output(4) = wsSRA.Cells(tr, "S").Value
output(5) = wsSRA.Cells(tr, "T").Value
output(6) = wsSRA.Cells(tr, "U").Value
month = wsSRA.Cells(tr, "AH").Value
typ1 = wsSRA.Cells(tr, "AI").Value
typ2 = wsSRA.Cells(tr, "AK").Value
If typ1 = "America" And typ2 = "Regi..." Then
wsCRA.Range("D45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ1 = "America" And typ2 = "Sam..." Then
wsCRA.Range("D97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ1 = "America" And typ2 = "RegSD" Then
wsCRA.Range("D149").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ1 = "America" And typ2 = "SamSD" Then
wsCRA.Range("D201").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)Etc, etc... same for Europe, Asia, and Africa area.
But it is not working, no value appearing in my chart.
Should I replicate the coding from "For Each c In wsSRA.Range.." till "Typ2..." for the second criteria ? Or should I create a d dimension ?
Thanks in advance for your good advice,
Regards,
Lomic6