Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA - drawing a chart on disjoint data

Posted on 2006-11-06
4
Medium Priority
?
1,073 Views
Last Modified: 2008-02-01
I need to draw a chart for a group of data that is disjoint, ie does not lie in adjacent cells. the categories are all listed in column "D" and the values are all available in column "H". For example the first category is listed in cell "D13" and the corresponding value is in cell "H13". Then the second category is in cell "D18" and the corresponding value is in cell "H18" and so on. There may be any number of categories. How do i draw a chart for this type of data?
0
Comment
Question by:sergeiweerasuriya
[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
  • 2
4 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 17884177
Sergei,

This is quite possible.  Are you row numbers going to be hard coded, or is there some criteria you choose to know to use rows 13,18,etc?

Here is an example using a specified (non-adjacent) range, it uses formula array notation to populate the series and title:

Sub SergeiExample()
 Dim RG As Range, CLL As Range
 Dim sValues As String, sTitles As String
 Set RG = Range("D13,D18,D27,D28,D33,D50")
 For Each CLL In RG.Cells
  If Len(sTitles) = 0 Then
   sTitles = "={""" & CLL.Text & """"
   sValues = "={" & CLL.Offset(0, 4).Value
  Else
   sTitles = sTitles & ",""" & CLL.Text & """"
   sValues = sValues & "," & CLL.Offset(0, 4).Value
  End If
 Next
 If Len(sTitles) > 0 Then
  sTitles = sTitles & "}"
  sValues = sValues & "}"
  Charts.Add
  ActiveChart.ChartType = xlColumnClustered '*** change as needed
  ActiveChart.SeriesCollection(1).XValues = sTitles
  ActiveChart.SeriesCollection(1).Values = sValues
  ActiveChart.SeriesCollection(1).Name = "=""Series Title"""
  ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart sheet name"
 End If
End Sub

The Offset statement gets the value, as column H is 4 columns to the right of D.  If you need any help with determining the range to use as source data, please just let me know how you decide which to use and we can incorporate that into the code.

Matt
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17884197
if the data is in every 5th row, do you simply want to continue pulling values until an empty row is encountered?

13, 18, 23, 28, 33... until then nth row is empty?

You will need to create a data array (in code), and the populate the arrys from the cells in the SS, and use the array as the source of the data for the chart.

Or you could map the cells where the data actuall resides (D/H 13, D/H 18...) into a hidden range of cells (AA/AB 1, AA/AB 2...) and use that 'hidden' range as the source of the data for the chart.  Your choice.

AW
0
 

Author Comment

by:sergeiweerasuriya
ID: 17885348
No.....the row numbers cannot be hard coded simply because there is no way to know how many rows there will be. This is a macro that will be run every month. These values are a total of each category. For instance in cell H13 will be the total of cars, and in the cell D13 and in the cells above it (in D column) will be the category "cars". Then from cell D14 to cell D18 will be the category "food" and in cell H18 will be the total of "food" and so on..... The macro should run for any number of categories and each category may have any number of rows. it is not in the sequence of 13,18,23,28,..etc.
0
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 17888963
Ok, try the following, should do exactly what you need:

Sub SergeiExample()
 Dim CLL As Range, sValues As String, sTitles As String
 For Each CLL In Range("D2", Cells(Rows.Count, 4).End(xlUp)).Cells 'change D2 to your first category cell
  If CLL.Text <> CLL.Offset(1, 0).Text And Len(CLL.Text) > 0 Then 'if it is the last cell of the category
   If Len(sTitles) = 0 Then
    sTitles = "={""" & CLL.Text & """"
    sValues = "={" & CLL.Offset(0, 4).Value
   Else
    sTitles = sTitles & ",""" & CLL.Text & """"
    sValues = sValues & "," & CLL.Offset(0, 4).Value
   End If
  End If
 Next
 If Len(sTitles) > 0 Then
  sTitles = sTitles & "}"
  sValues = sValues & "}"
  Charts.Add
  ActiveChart.ChartType = xlColumnClustered '*** change as needed
  ActiveChart.SeriesCollection(1).XValues = sTitles
  ActiveChart.SeriesCollection(1).Values = sValues
  ActiveChart.SeriesCollection(1).Name = "=""Series Title"""
  ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart sheet name"
 End If
End Sub

It starts looking in D2 (change as needed, though it will ignore blanks), and when Dn <> Dn+1 then it uses that as the category and uses Hn as the value.  If Hn doesn't have the total, and you want the sum of Hn-x:Hn then let us know as well and we can change it for you.

Matt
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes

715 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