How to populate Charts based on many criteria ???

Lomic6

New member
Joined
Jul 21, 2011
Messages
19
Reaction score
0
Points
0
Hi All,

I would need help for me to understand how I could manage populating many charts with sorted data based on different criteria like Route, Carrier, Consignee, Activity ?

e.g. Let's say we have a route PAR-TYO. Let's say that for the same route we use the carrier AF and JL.
i need to create one chart for the cycle time on PAR-TYO with AF and one chart PAR-TYO with JL.
I need to create also another chart with the volume carried on PAR-TYO with AF and another one PAR-TYO with JL.

How could I automated it ?

I have wrote the following code:

Code:
Sub myChartDataLSCSC()
Dim c As Range, typ As String, typ2 As String, typ3 As String, typ4 As String
Dim tr As Long, month As Long, year As Long
Dim output(6)
Dim wsSR As Worksheet, wsCS As Worksheet
Set wsSR = Sheets("LSCSC")
Set wsCS = Sheets("ChartLSCSC")
Application.ScreenUpdating = False
wsSR.Activate
wsSR.Range("A1").AutoFilter Field:=44, Criteria1:="<>"
For Each c In wsSR.Range("AZ:AZ").SpecialCells(12)
tr = c.Row
If tr <> 1 Then
'Output for the Cycle Time
output(1) = wsSR.Cells(tr, "Q").Value
output(2) = wsSR.Cells(tr, "R").Value
output(3) = wsSR.Cells(tr, "S").Value
output(4) = wsSR.Cells(tr, "T").Value
output(5) = wsSR.Cells(tr, "U").Value
output(6) = wsSR.Cells(tr, "V").Value
year = wsSR.Cells(tr, "AK").Value
month = wsSR.Cells(tr, "AL").Value
'"AR" corresponds to the route e.g. PAR-TYO
typ = wsSR.Cells(tr, "AR").Value
'"AP" corresponds to the Shipping plant
typ2 = wsSR.Cells(tr, "AP").Value
'"AQ" corresponds to the Receiving plant
typ3 = wsSR.Cells(tr, "AQ").Value
'"AT" corresponds to the Carrier e.g. AF or JL
typ4 = wsSR.Cells(tr, "AT").Value

If typ = "SIN-TYO" And typ4 = "JL" Then
wsCS.Range("D45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "SIN-TYO" And typ4 = "AF" Then
wsCS.Range("D97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
Else
GoTo Exit_Sub
End If

'Output for the volume
output(1) = wsSR.Cells(tr, "W").Value
output(2) = wsSR.Cells(tr, "X").Value
output(3) = wsSR.Cells(tr, "Y").Value
output(4) = wsSR.Cells(tr, "AE").Value
output(5) = wsSR.Cells(tr, "AC").Value
output(6) = wsSR.Cells(tr, "AF").Value

If typ = "SIN-TYO" And typ4 = "JL" Then
wsCS.Range("X45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "SIN-TYO" And typ4 = "AF" Then
wsCS.Range("X97").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

Unfortunately my chart are not populated as expected.
In addition for my reporting I will have hundreds of chart to do and i dont think that to write a code with the IF condition will be worth coz i will have to write hundreds of line, and i may face memory issue.

Thanks for your feedback,

Best Regards,
Lomic6
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
Lomic, can you post a sample? Without understanding the filtering and looping with some data it will be difficult to assist.

Jesse
 

Lomic6

New member
Joined
Jul 21, 2011
Messages
19
Reaction score
0
Points
0
Hi Jesse,

Sorry for my late reply since i was away for many days.

As requested, please find as attached a sampling of my file with the coding.

Actually I am more focused on how to populate the charts in the "ChartLSCSC" sheet.
For this file I have just put few charts, but once the file will be completed I will have something like 700 charts to manage.
That's why I am a bit concerned with the coding [If.. ElseIf... Then] because the number of line to write with all the possible combination will be very very huge.

I take this opportunity to wish to Everyone in the forum an Happy New Year 2012 !!! :)

Thanks,
Lomic6
 

Attachments

  • Sample of Reporting Data for Testing Purpose_Rev2.2.zip
    861.7 KB · Views: 20

Lomic6

New member
Joined
Jul 21, 2011
Messages
19
Reaction score
0
Points
0
Hi All,

Can anyone help me on this subject ?

Thanks,
Lomic6
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Lomic,

I'm curious why the use of VBA to create them. Just too many to do, or are they always changing, or...?
 
Top