Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

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

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
chtullu135
Asked:
chtullu135
  • 7
  • 4
1 Solution
 
gowflowCommented:
Who told you it is working
A:A,D:D is non contiguous right ?

See attached.
gowflow
Chart.xlsm
0
 
chtullu135Author Commented:
Yes it's non continuous
0
 
chtullu135Author Commented:
I cannot opent your attachment
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chtullu135Author Commented:
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
 
gowflowCommented:
Well your file is working no problem how come you can't open it ?

here is it again
gowflow
Chart-V01.xlsm
0
 
chtullu135Author Commented:
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
 
gowflowCommented:
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
 
chtullu135Author Commented:
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
 
chtullu135Author Commented:
"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
 
gowflowCommented:
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
 
chtullu135Author Commented:
It now works.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now