Solved

Excel VBA - drawing a chart on disjoint data

Posted on 2006-11-06
4
1,002 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
  • 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
bigHeights  challenge 13 56
sumDigits  challenge 7 62
advertisement module in core php 4 94
recursion example 16 70
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

9 Experts available now in Live!

Get 1:1 Help Now