Solved

Excel VBA - drawing a chart on disjoint data

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to send memory stream from ics Client To ics server ? 11 110
Looking for example pivot year code used in Y2K 4 68
Recommendation vb6 to vb.net or others 14 149
Math Equation 23 86
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…
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.
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 …
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…

773 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