Excel Help with visual basic code in Excel 2003

[jdukin]

Hi all,

I am using the code below trying to add a Column/Line graph to a spreadsheet, but it's loading a regular old column graph. Do I have things in the wrong order or am I missing something?

'==Monthly Spread
'==Determine Range
StartCol = Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).Column
SecRtnCol = Sheets("Data").Cells.Find(What:=sSecNum & "_SecRtn", LookAt:=xlWhole).Column
ABCStartCol = Application.WorksheetFunction.VLookup(StartCol, Sheets("LookUp").Range("E:F"), 2, 0)
ABCSecRtnCol = Application.WorksheetFunction.VLookup(SecRtnCol, Sheets("LookUp").Range("E:F"), 2, 0)
If Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).Offset(1, 0).Value <> "" Then
StartRow = Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).Row + 1
EndRow = Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).End(xlDown).Row
Else
StartRow = Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).End(xlDown).Row
EndRow = Sheets("Data").Cells.Find(What:=sSecNum & "_SecQSpread", LookAt:=xlWhole).End(xlDown).End(xlDown).Row
End If
'==Create Chart
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("A2:A" & sDateEndRow & "," & ABCStartCol & StartRow & ":" & ABCStartCol & EndRow & "," & ABCSecRtnCol & StartRow & ":" & ABCSecRtnCol & EndRow), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sector Results"
'==Name Chart
ActiveSheet.ChartObjects(Mid(ActiveChart.Name, Len("Sector Results") + 2, 1000)).Name = "QS Chart" & iCnt - 2
'==Format Chart
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.HasLegend = False

.PlotArea.Top = 1
.PlotArea.Height = 10000
.PlotArea.Left = 1
.PlotArea.Width = 10000
.PlotArea.Interior.ColorIndex = xlNone
End With
'==Format X Axis
With ActiveChart.Axes(xlCategory)
.TickLabels.Font.Name = "Arial"
.TickLabels.Font.Size = 8
.TickLabels.Orientation = xlUpward
.TickLabelPosition = xlLow
.TickLabelSpacing = 12
End With
'==Format Y Axis
ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "0%"
'==Put Chart in Place
With ActiveSheet.Shapes(Mid(ActiveChart.Name, Len("Sector Results") + 2, 1000))
.Top = sChartTop
.Left = 425
.ScaleWidth 0.77, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.45, msoFalse, msoScaleFromTopLeft
End With


Any help/suggestions would be greatly appreciated! Thanks!

JDM

 

[guest_9323wk]

Hi,

Thanks for your post. Suggest you have a look here.

Regards,

Wiz

 

[jdukin]

Thanks Wiz - sorry I couldn't open the link but I have figured out the problem myself, just had to re-order a couple lines of code:


ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & StartRow & ":A" & EndRow & "," & ABCStartCol & StartRow & ":" & ABCStartCol & EndRow & "," & ABCSecRtnCol & StartRow & ":" & ABCSecRtnCol & EndRow), PlotBy:=xlColumns
'ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & StartRow & ":A" & EndRow), PlotBy:=xlColumns
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.SeriesCollection(1).XValues = "=Data!R" & StartRow & "C1:R" & EndRow & "C1"
ActiveChart.SeriesCollection(1).Values = "=Data!R" & StartRow & "C" & StartCol & ":R" & EndRow & "C" & StartCol
ActiveChart.SeriesCollection(2).Values = "=Data!R" & StartRow & "C" & SecRtnCol & ":R" & EndRow & "C" & SecRtnCol

 

[guest_9323wk]

Thanks Wiz - sorry I couldn't open the link but I have figured out the problem myself, just had to re-order a couple lines of code:


ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & StartRow & ":A" & EndRow & "," & ABCStartCol & StartRow & ":" & ABCStartCol & EndRow & "," & ABCSecRtnCol & StartRow & ":" & ABCSecRtnCol & EndRow), PlotBy:=xlColumns
'ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & StartRow & ":A" & EndRow), PlotBy:=xlColumns
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
ActiveChart.SeriesCollection(1).XValues = "=Data!R" & StartRow & "C1:R" & EndRow & "C1"
ActiveChart.SeriesCollection(1).Values = "=Data!R" & StartRow & "C" & StartCol & ":R" & EndRow & "C" & StartCol
ActiveChart.SeriesCollection(2).Values = "=Data!R" & StartRow & "C" & SecRtnCol & ":R" & EndRow & "C" & SecRtnCol

Well done glad you got it fixed, link should be ok now.

Regards,

Wiz