Solved

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

Posted on 2015-02-03
12
81 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Dynamic Dropdown List in Excel excluding Blanks 5 35
Tags from access to excel 3 27
Match formula returns N/A 5 25
Excel Save As Status Box will not go away 6 16
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now