Solved: Chart Creation

[CatDaddy]

getting out of range error on red line...seems like this should work to me

[vba]Sub STEP5_Trending()
Dim LCol As Integer
Dim i As Integer
Dim dataRng(1 To 5) As Range
Dim title(1 To 5) As String
ActiveWorkbook.Sheets("Daily Totals").Activate
Range("A1").Select
For i = 2 To 40
If Cells(2, i).Value = "" Then
LCol = i
Exit For
End If
Next i
With ActiveSheet
Set dataRng(1) = Range(.Cells(1, 1), .Cells(13, LCol))
Set dataRng(2) = Range(.Cells(16, 1), .Cells(28, LCol))
Set dataRng(3) = Range(.Cells(31, 1), .Cells(43, LCol))
Set dataRng(4) = Range(.Cells(46, 1), .Cells(58, LCol))
Set dataRng(5) = Range(.Cells(61, 1), .Cells(73, LCol))
title(1) = Range("A1").Text
title(2) = Range("A16").Text
title(3) = Range("A31").Text
title(4) = Range("A46").Text
title(5) = Range("A61").Text
End With
For i = 1 To 5
ChartMaking dataRng(i), title(i)
Next i
End Sub

Private Sub ChartMaking(dataRng As Range, title As String)
ActiveWorkbook.Sheets("Charts").Activate
Charts.Add
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.HasTitle = True
.ChartTitle.Text = title
.SetSourceData _
Source:=Sheets("Daily Total").dataRng, _
PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Charts"

.ChartWizard _
HasLegend = True, _
CategoryTitle:="Dates", _
ValueTitle:="Tickets"

End With

End Sub[/vba]

something wrong with my range declaration?

[CatDaddy]

is this more what i should have? i imagine its a problem with the range declaration, however i debugged and the range is selecting properly?
[VBA]

Set dataRng(1) = Range(""" & .Cells(1, 1).Address & ":" & .Cells(13, LCol).Address & """)
[/VBA]

[Bob Phillips]

DataRng is a vector, so you have to reference it by index, its occurrence in the variant.

[CatDaddy]

DataRng is a vector, so you have to reference it by index, its occurrence in the variant.

that wasnt my problem i dont think, its working now but im not really sure why!!!!!

[vba]Sub STEP5_Trending()
Dim LCol As Integer
Dim i As Integer
Dim dataRng(1 To 5) As Range
Dim title(1 To 5) As String
ActiveWorkbook.Sheets("Daily Totals").Activate
Range("A1").Select
For i = 2 To 40
If Cells(2, i).Value = "" Then
LCol = i - 1
Exit For
End If
Next i
With ActiveSheet
Set dataRng(1) = Range(.Cells(1, 1), .Cells(13, LCol))
Set dataRng(2) = Range(.Cells(16, 1), .Cells(28, LCol))
Set dataRng(3) = Range(.Cells(31, 1), .Cells(43, LCol))
Set dataRng(4) = Range(.Cells(46, 1), .Cells(58, LCol))
Set dataRng(5) = Range(.Cells(61, 1), .Cells(73, LCol))

title(1) = Range("A1").Text
title(2) = Range("A16").Text
title(3) = Range("A31").Text
title(4) = Range("A46").Text
title(5) = Range("A61").Text
End With
For i = 1 To 5
ChartMaking dataRng(i), title(i)
Next i
End Sub

Private Sub ChartMaking(dataRng As Range, title As String)
Charts.Add 'Create a chart sheet
With ActiveChart 'Set chart properties
.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Text = title
.HasLegend = True
.Legend.Position = xlRight
.Axes(xlCategory).MinorTickMark = xlOutside
.Axes(xlValue).MinorTickMark = xlOutside
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Date"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Tickets"

.Location Where:=xlLocationAsObject, Name:="Charts"
End With

End Sub
[/vba]

it seems like im not even using the data range but its working correctly....

EDIT: its not really working correctly. its churning out 5 of the same chart, but it is using hte correct first data range for all 5

[Bob Phillips]

It seems it was to me, you are now referencing the vector by index as I said.

[CatDaddy]

It seems it was to me, you are now referencing the vector by index as I said.

i'm sorry i don't follow, what changed in the way i was referencing the array? and it doesnt actually work now it just uses dataRng(1) to populate 5 appropriatly named charts...

[CatDaddy]

ok so this makes the first chart correctly and then says there is an out of range error on the .SetSourceData

application or user defined error:

[vba]Sub STEP5_Trending()
Dim SrcSheet As Worksheet
Dim LCol As Integer
Dim i As Integer
Dim CurTitle As String
Dim CurRng As Range
Dim dataRng(1 To 5) As Range
Dim title(1 To 5) As String
Set SrcSheet = ActiveWorkbook.Sheets("DailyTotals")
SrcSheet.Activate
Range("A1").Select
For i = 2 To 40
If Cells(2, i).Value = "" Then
LCol = i - 1
Exit For
End If
Next i
With ActiveSheet
Set dataRng(1) = Range(.Cells(1, 1), .Cells(13, LCol))
Set dataRng(2) = Range(.Cells(16, 1), .Cells(28, LCol))
Set dataRng(3) = Range(.Cells(31, 1), .Cells(43, LCol))
Set dataRng(4) = Range(.Cells(46, 1), .Cells(58, LCol))
Set dataRng(5) = Range(.Cells(61, 1), .Cells(73, LCol))
title(1) = Range("A1").Text
title(2) = Range("A16").Text
title(3) = Range("A31").Text
title(4) = Range("A46").Text
title(5) = Range("A61").Text
End With
For i = 1 To 5
CurTitle = title(i)
Set CurRng = dataRng(i)
ChartMaking CurRng, CurTitle
Next i
End Sub
Private Sub ChartMaking(CurRng As Range, CurTitle As String)
Dim NewChart As Chart
Set NewChart = ThisWorkbook.Charts.Add
With NewChart 'Set chart properties

.SetSourceData _
Source:=Worksheets("DailyTotals").Range("CurRng"), _
PlotBy:=xlRows

.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Text = CurTitle
.HasLegend = True
.Legend.Position = xlRight
.Axes(xlCategory).MinorTickMark = xlOutside
.Axes(xlValue).MinorTickMark = xlOutside
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Date"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Tickets"

End With
End Sub
[/vba]

[Bob Phillips]

Try

[vba]
.SetSourceData Source:=CurRng, PlotBy:=xlRows [/vba]

[CatDaddy]

Try

[vba]
.SetSourceData Source:=CurRng, PlotBy:=xlRows [/vba]

once again danke schoen