Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing blocks of cells for charts
day 2 of VBA...
I've managed to create a worksheet ("Summary") containing blocks of cells separated by empty rows. Now I'm struggling with the next bit. In the first block, cell A1 contains the title of a chart, B1:Q5 contains the chart data. The other blocks are different sizes but with the same basic layout. I've also have a subroutine DoChrt for drawing a chart (see below). What I would like to do now is to have a subroutine that takes each block of data, identifies the chart title (always top left in the block) and source data (variable size but same position relative to title), makes the chart, then moves on to the next block. I'd be very grateful if anyone has any ideas how to set this up. Thanks Simon Sub DoChrt() Dim Cht As Chart Set Cht = Charts.Add Cht.SetSourceData Source:=Sheets("Summary").Range("B1:Q5") With Cht .HasTitle = True .ChartTitle.Characters.Text = Sheets("Summary").Range("A1") End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing blocks of cells for charts
Sub DoChrt()
Dim Cht As Chart Dim rng As Range, ar As Range Dim Data As Range, titlecell As Range With Worksheets("Summary") Set rng = .Columns(1).SpecialCells(xlConstants) End With For Each ar In rng.Areas Set titlecell = ar(1) Set Data = rng.Parent.Range(titlecell(2, 1), _ titlecell(2, 1).End(xlToRight).End(xlDown)) Set Cht = Charts.Add Cht.SetSourceData Source:=Data With Cht .HasTitle = True .ChartTitle.Characters.Text = titlecell End With Next End Sub -- Regards, Tom Ogilvy wrote in message oups.com... day 2 of VBA... I've managed to create a worksheet ("Summary") containing blocks of cells separated by empty rows. Now I'm struggling with the next bit. In the first block, cell A1 contains the title of a chart, B1:Q5 contains the chart data. The other blocks are different sizes but with the same basic layout. I've also have a subroutine DoChrt for drawing a chart (see below). What I would like to do now is to have a subroutine that takes each block of data, identifies the chart title (always top left in the block) and source data (variable size but same position relative to title), makes the chart, then moves on to the next block. I'd be very grateful if anyone has any ideas how to set this up. Thanks Simon Sub DoChrt() Dim Cht As Chart Set Cht = Charts.Add Cht.SetSourceData Source:=Sheets("Summary").Range("B1:Q5") With Cht .HasTitle = True .ChartTitle.Characters.Text = Sheets("Summary").Range("A1") End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing blocks of cells for charts
perfect - thanks very much
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stacked charts with separate blocks | Charts and Charting in Excel | |||
Copying Blocks of Cells | Excel Discussion (Misc queries) | |||
SUMIFs for particular cells in blocks of data | Excel Worksheet Functions | |||
Stop selecting blocks of cells | New Users to Excel | |||
how to split address blocks across multiple cells | Excel Worksheet Functions |