Excel Charts & VBA
 

Hi folks,
I'm baffled- I'm writing a macro to pull a data table into an exce
sheet & create a bargraph of those results. The number of records i
the data table are variable (say between 2 and 50).

I'm trying to define a dynamic bar graph using VBA. The X values ar
in col C and the text Y vlaues are in col A. (both start in row 3) Th
values in column B are necessary, but have nothing to do with th
graph. I'm having trouble using variables to define the source data a
two, non adjacent ranges.

I've had problems just including the 50 cells in the data table as i
includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cel
C3. It just numbers them on the Y axis, not by the corresponding tex
values....

Any ideas?
Thanks
pim

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A" ))
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Graph.Chart.SetSourceDat
Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered
ActiveSheet.ChartObjects(1).Activate

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = True
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
.TickLabelPosition = xlHigh
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End Wit

--
Pi
-----------------------------------------------------------------------
Pim's Profile: http://www.excelforum.com/member.php...fo&userid=2756
View this thread: http://www.excelforum.com/showthread.php?threadid=47113