Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA - drawing a chart on disjoint data

Posted on 2006-11-06
4
Medium Priority
?
1,083 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Simple Linear Regression

877 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