Specifying which axes Excel uses to plot a graph

[Andreww]

Hi - I found some good code below to create a chart. The input to the
chart is a grid of data. I can't see why, but on one graph it plots
correctly with time being the x axis value and sometimes the other
(user selected) variable.

The grid always has same number of columns (time in months) but
dependent on user selection the number of rows may vary.

Any ideas?

Thanks

Andrew

Sub CreateChart()
'************** Creates chart on detail sheet *****

Sheets("Detail").Select

' Deletes existing chart
Call delete_Chart

Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range

Dim end_col As String
Dim end_val As Integer
Dim chrt_top As Integer


' ************** If adding dim need to add in value for chrt_top
here
' These have to be calculated manually
' based on rowheight being 15, find which row num you want graph
to start at and * 15
dim_name = Sheets("admin").Range("I2").Value

Select Case dim_name
Case "product"
chrt_top = 180
Case "vintage"
chrt_top = 555
Case "source"
chrt_top = 255
Case "segment"
chrt_top = 255
End Select


' Gets values to define edges of grid
end_col = Sheets("admin").Range("b9").Value
end_val = Sheets("admin").Range("b11").Value

With ActiveSheet
' What range should chart cover
Sheets("Detail").Range("B6:" & end_col & end_val - 1).Select

' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub

' define chart data
Set myDataRange = Selection

' (AW - 18NOV09) Redefine range again but now for where to put
chart
Sheets("Detail").Range("B14:H" & end_val - 1).Select

Set myChtRange = Selection

' Cover chart range with chart - plonk chart where specified above
'Set objChart = .ChartObjects.Add( _
' Left:=myChtRange.Left, Top:=myChtRange.Top, _
' Width:=myChtRange.Width, Height:=myChtRange.Height)

' This code positions the chart on the sheet
' Left = in from left hand side
' Width = across chart
' Top = From top edge of sheet
' Height = height of chart
Set objChart = .ChartObjects.Add( _
Left:=48, _
Width:=575, _
Top:=chrt_top, _
Height:=225)


' Put all the right stuff in the chart
With objChart.Chart
.ChartArea.AutoScaleFont = False
'.ChartType = xlXYScatterLines
.ChartType = xlLine
.SetSourceData Source:=myDataRange
.HasTitle = True
.ChartTitle.Characters.Text = "My Title"
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 10
With .Axes(xlCategory, xlPrimary)
.HasTitle = False
' Commented out as don't want this
' With .AxisTitle
' .Characters.Text = "My X Axis"
' .Font.Size = 8
' .Font.Bold = True
' End With
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = "My Y Axis"
.Font.Size = 8
.Font.Bold = True
End With
End With
End With
End With
End Sub