Solved

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

Posted on 2015-02-03
12
92 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
  • 7
  • 4
12 Comments
 
LVL 29

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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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 29

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 29

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 29

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

828 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