Solved

Excel VBA - drawing a chart on disjoint data

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
BATCH to EXE Converter 2 71
Do Wend Macro not working 22 58
Java array 10 65
C# Windows Form Navigation - Total Beginner 9 49
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

730 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