Problem with chart range as a variable

[jaybro62]

Guys-

I have a sheet that has a long list of data. I am tying to select a range of cells and chart them. With the code I have, I know the starting row and the ending row. I am now trying to move those variables to the chart range. It errors on the line where the "Cells" reference is. Runtime 1004 Error.
Any ideas?

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim SN As String, SNN As String
Dim SRNG As Long, ERNG As Long
SN = "" ' make sure the varibles are blank
SNN = ""
shtnm = Worksheets("Charts").Range("Z1").Value ' grab the sheet name from stored location
Worksheets(shtnm).Activate
ActiveSheet.Range("H2").Select
SN = ActiveCell.Value
SRNG = ActiveCell.Row ' set the starting row
Do Until IsEmpty(ActiveCell) ' loop through the cells and find when the data changes
SNN = ActiveCell.Value
If SN = SNN Then
ActiveCell.Offset(1, 0).Select
Else
ERNG = ActiveCell.Row - 1 ' set the ending row
' create the chart -
Worksheets("Charts").Select
ActiveSheet.Range("B7").Select
Dim objCht As ChartObject
With ActiveSheet
Set objCht = .ChartObjects.Add(.Cells(7, 2).Left, .Cells(7, 2).Top, 700, 300) ' add the chart
End With
With objCht.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets(shtnm).Range(Cells(SRNG, 5), Cells(ERNG, 8)), PlotBy:=xlColumns '<< THIS IS THE LINE THAT ERRORS>
.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
End With
End If
Loop
Worksheets("Charts").Select
Application.ScreenUpdating = True
End Sub

 

[VoG]

Try

.SetSourceData Source:=Sheets(shtnm).Range(Sheets(shtnm).Cells(SRNG, 5), Sheets(shtnm).Cells(ERNG, 8)), PlotBy:=xlColumns