I'm working on code to create a chart that takes 96 values for one data point. Following is the code I'm working on, but basically my problem is that using a string variable to hold the worksheet name and then appending the range address to create a series on a chart isn't working. I need to be able to append the range address on the end of the worksheet name so I can loop through all of the data points. The NewSeries.Values won't accept my range in code, but takes the same range fine manually.

Dim rng, rngName, rngXAxes As Range
Dim strFormula, strRange As String
Set rng = Range("D297") 'one day of data
Set rngName = Range("B2") 'date of data
Set cht = Charts.Add 'adds chart sheet
Set scSeries = cht.SeriesCollection
strRange = rng.Address
strFormula = "='" & strSheet & "'!" & strRange
days = ((LastRow - 1) / 96)

'Delete default series
For i = scSeries.Count To 1 Step -1
scSeries(i).Delete
Next i
i = 1
For i = 1 To days
strName = rngName.Value
With scSeries.NewSeries
.Name = strName
.Values = strFormula 'here is where it stops
End With
Set rng = rng.Offset(96, 0)
Set rngName = rngName.Offset(96, 0)
Next i
cht.ChartType = xlSurface

Any suggestions will be much appreciated!

Thanks,

Kathy