Solved

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

Posted on 2015-02-03
12
100 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

732 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