Solved

How to set the chart SetSourceData Source to non-contiguous columns

Posted on 2015-02-03
12
107 Views
Last Modified: 2015-02-09
Hello Again,
The following code, I wrote, works well when rng refers to adjacent columns.  However the chart is not populated when I set the SourceData to non-contiguos columns.  

Private Sub CreateNE_TotalPercentItemsAvailableChart(objWS As Object)
Dim objXLSheet As Object
Dim objXlChart As Object
Dim objXlDataSheet As Object
Dim rng As Object
Dim rngChart1 As Object
Dim rngChart2 As Object
Set objXLSheet = objWS
Dim y As Long


Set objXlDataSheet = objXlBook.Worksheets("Summary")
Set rng = objXlDataSheet.Range("A:A,D:D")
y = objXlDataSheet.UsedRange.Rows.Count
y = y + 150

Set objXlChart = objXLSheet.ChartObjects.Add(Left:=75, Width:=750, Top:=y, Height:=300).Chart

    With objXlChart
        .ChartType = 4  'xlLine
        '.SetSourceData Source:=rng
        .SetSourceData Source:=rng
        .PlotBy = 2
        .HasTitle = True
        .ChartTitle.Text = "Norgren Express" & vbCrLf & "Total Items % Available"
        .HasLegend = False
        .Axes(2).TickLabels.NumberFormat = "0%"
        .Axes(2).TickLabels.Orientation = 0
        .Axes(1).TickLabels.Orientation = 60
        .ChartStyle = 31
    End With

End Sub

Open in new window

0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 30

Accepted Solution

by:
gowflow earned 0 total points
ID: 40588067
Who told you it is working
A:A,D:D is non contiguous right ?

See attached.
gowflow
Chart.xlsm
0
 

Author Comment

by:chtullu135
ID: 40589714
Yes it's non continuous
0
 

Author Comment

by:chtullu135
ID: 40589727
I cannot opent your attachment
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:chtullu135
ID: 40589733
I changed line '.SetSourceData Source:=rng to   .SetSourceData Source:=objXlDataSheet.Range("A:A,D:D")


Private Sub CreateNE_TotalPercentItemsAvailableChart(objWS As Object)
Dim objXLSheet As Object
Dim objXlChart As Object
Dim objXlDataSheet As Object
Dim rng As Object
Dim rngChart1 As Object
Dim rngChart2 As Object
Set objXLSheet = objWS
Dim y As Long


Set objXlDataSheet = objXlBook.Worksheets("Summary")
Set rng = objXlDataSheet.Range("A:A,D:D")
y = objXlDataSheet.UsedRange.Rows.Count
y = y + 150

Set objXlChart = objXLSheet.ChartObjects.Add(Left:=75, Width:=750, Top:=y, Height:=300).Chart

    With objXlChart
        .ChartType = 4  'xlLine
        '.SetSourceData Source:=rng
        .SetSourceData Source:=objXlDataSheet.Range("A:A,D:D")
        .PlotBy = 2
        .HasTitle = True
        .ChartTitle.Text = "Norgren Express" & vbCrLf & "Total Items % Available"
        .HasLegend = False
        .Axes(2).TickLabels.NumberFormat = "0%"
        .Axes(2).TickLabels.Orientation = 0
        .Axes(1).TickLabels.Orientation = 60
        .ChartStyle = 31
    End With

End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:gowflow
ID: 40589745
Well your file is working no problem how come you can't open it ?

here is it again
gowflow
Chart-V01.xlsm
0
 

Author Comment

by:chtullu135
ID: 40589796
I had to save the file to my drive, open excel, and then open the file.  

"Who told you it is working
A:A,D:D is non contiguous right ?"

It looked like it was working because the chart was displaying data.  I may be wrong but to me, when the chart is displaying the correct data, it is working.  Of course I may be missing something
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40589803
I meant who told you it is NOT working !!!

Your code is fine and A:A, D:D is a contiguous range right ?
gowflow
0
 

Author Comment

by:chtullu135
ID: 40589805
From what I can see, you made the desired columns continguous.  The client does not want the columns to be contiguous.  The client is adamant on that.
0
 

Author Comment

by:chtullu135
ID: 40589815
"I meant who told you it is NOT working !!!"  No need for the exclamation marks, I get it. you made a mistake

"Your code is fine and A:A, D:D is a contiguous range right ?"
Columns A;A and D:D are not next to each other.  columns B;B and C:C are between them them.
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40590584
Sorry do not understand neither your comments nor your closing awarding yourself 0 points ? Did I miss something ? Maybe we are having a mis-communication here.

My understanding of contiguous is that they do not follow each others. so for me A:A,D:D is 2 ranges that do not follow each others then contiguous. and your question was how can I get the graph to display for non contiguous ranges and for me I tried the code you posted and it was working fine.

So please If I mis-understood your request then kindly clarify it.

Thank you
gowflow
0
 

Author Comment

by:chtullu135
ID: 40597967
It now works.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question