Results 1 to 5 of 5

Thread: How to populate Charts based on many criteria ???

  1. #1

    Exclamation How to populate Charts based on many criteria ???



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Lomic, can you post a sample? Without understanding the filtering and looping with some data it will be difficult to assist.

    Jesse

  3. #3

    Exclamation

    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
    Attached Files Attached Files

  4. #4
    Hi All,

    Can anyone help me on this subject ?

    Thanks,
    Lomic6

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Lomic,

    I'm curious why the use of VBA to create them. Just too many to do, or are they always changing, or...?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •