Help with <Method 'Cells' of object '_Global' failed> please

[maxhetzer]

Hi everyone,

I am trying to create a macro that would automatically create a chart in a spreadsheet with three worksheets with range for X and Y values to be found automatically.

I run into this error and can't find how to solve the problem, below is the code with the error

Sub createChart()
Dim counter As Integer
Dim worksheetNum As Integer
Dim trialName As String
Dim trial As String
Dim trNum As String


'initialize variables
counter = 0
worksheetNum = 0
trialName = ""
trial = "Trial"

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.Name = "modulus"

For counter = 1 To 3
worksheetNum = counter
trNum = 4 - counter
trialName = trial + " " + trNum

Worksheets(counter).Select

'get the data from true strain
flag = 0
i = 1 'initialize i
Do Until flag = 1
i = i + 1
ActiveSheet.Cells(i, 11).Select
If ActiveCell.Text = "True strain %" Then Exit Do
Loop

flag = 0 'reset flag
startRow = i + 1 'remember starting position for the select range
i = startRow 'reset counter to the startRow position

Do Until flag = 1

ActiveSheet.Cells(i, 11).Select
If ActiveCell.Value <= 0.42 And ActiveCell.Value >= 0.38 Then Exit Do
i = i + 1
Loop

endRow = i 'set endRow as the ending row index
Range(Cells(startRow, 15), Cells(endRow, 15)).Select

Charts("modulus").Select
With ActiveChart.SeriesCollection.NewSeries
.Name = trialName
' Problem occurs here
.XValues = Worksheets(counter).Range(Cells(startRow, 11), Cells(endRow, 11))
.Values = Worksheets(counter).Range(Cells(startRow, 15), Cells(endRow, 15))
End With

' ActiveChart.SeriesCollection(counter).XValues = ActiveSheet.Range(Cells(startRow, 11), Cells(endRow, 11))
' ActiveChart.SeriesCollection(counter).Values = ActiveSheet.Range(Cells(startRow, 15), Cells(endRow, 15))
' ActiveChart.SeriesCollection(counter).Name = trialName

Next counter


ActiveChart.Location Where:=xlLocationAsNewSheet


Any suggestions as to how to solve this problem would be greatly appreciated.

Thank you,

Max.