Solved: Range help

[ravinggenius]

I have a method that accepts for its third parameter a range. When I specify it with the Range("A1:B1") syntax, everything works as expected (it draws a small sparkline-type graph). However I am not able to use this syntax, as I only have the index numbers of the starting/ending rows/columns for the range. (I am creating multiple ranges in a loop and each range should be graphed.)

Public Sub test_create_sparkline_chart()
Call create_sparkline_chart("Sheet1", "B2", Worksheets("Sheet2").Range("A2:A7")) 'will work
Call create_sparkline_chart("Sheet1", "B2", Worksheets("Sheet2").Range(Cells(2, 1), Cells(7, 1))) 'will not work
Call create_sparkline_chart("Sheet1", "B2", Worksheets("Sheet2").Cells(2, 1).Offset(7, 0)) 'will not work
End Sub
I will greatly appreciate any help I can get. Thanks.

[Bob Phillips]

Public Sub test_create_sparkline_chart()
With Worksheets("Sheet2")

Call create_sparkline_chart("Sheet1", "B2",Range(.Cells(startline, 1), .Cells(endline, 1)))
End With
End Sub

[stanleydgrom]

ravinggenius,

Can we see the code for "create_sparkline_chart"?


Have a great day,
Stan

[ravinggenius]

Thanks for the quick responses.

@xld: I am getting a run-time error, 1004

@stanleydgrom:Public Sub create_sparkline_chart(str_sheetName As String, str_cell As String, rng_sourceData As Range)
Dim dbl_chartLeft As Double
Dim dbl_chartTop As Double
Dim dbl_chartWidth As Double
Dim dbl_chartHeight As Double

dbl_chartLeft = Worksheets(str_sheetName).Range(str_cell).Left
dbl_chartTop = Worksheets(str_sheetName).Range(str_cell).Top
dbl_chartWidth = 72
dbl_chartHeight = 37.5

With Worksheets(str_sheetName)
Dim newChartObject As ChartObject
Set newChartObject = .ChartObjects.Add(dbl_chartLeft, dbl_chartTop, dbl_chartWidth, dbl_chartHeight)

With newChartObject.Chart
'.Name = str_chartName
.ChartType = XlChartType.xlLine
.ChartArea.Border.LineStyle = XlLineStyle.xlLineStyleNone
.SetSourceData Source:=rng_sourceData
.PlotArea.Border.LineStyle = XlLineStyle.xlLineStyleNone
.PlotArea.Interior.Color = RGB(255, 255, 255)
.PlotArea.Left = -5
.PlotArea.Top = 0
.PlotArea.Width = dbl_chartWidth
.PlotArea.Height = dbl_chartHeight - 10
.SeriesCollection(1).Smooth = True
.SeriesCollection(1).Border.LineStyle = XlLineStyle.xlContinuous
.SeriesCollection(1).Border.Weight = XlBorderWeight.xlMedium
.HasLegend = False

For Each a In .Axes
a.Border.LineStyle = XlLineStyle.xlLineStyleNone
a.HasMajorGridlines = False
a.HasMinorGridlines = False
a.MajorTickMark = XlTickMark.xlTickMarkNone
a.MinorTickMark = XlTickMark.xlTickMarkNone
a.TickLabels.Delete
Next a

End With
End With
End Sub

[Bob Phillips]

What did you use for startline and endline?

[ravinggenius]

Thank you all for your help. It turned out to be a scope issue. Once I fully qualified everything, it worked fine.

[Fabrice]

Hi RavingGenius,

have you tried sparklines-excel.blogspot.com ?
It is an open source VBA code for Sparklines...

give it a try and let me know what you think.

F