Hi Helpers,
I create a chart that use StatterSmoothNoMarkers type. The xvalue is fixed @ location A2: A100.
But the Series value is depended on number of files opened. If they open 3 files, i have 3*3=9 columns data and so on. I got the code from online and modified it for my case. I dont know how to put all collected data together to make them become data source. Could you please help me out.
Thanks. Here is the line with the error:
Set rngChtData = ("A2:A100") , ("2"): intCol5.Select ------->error right here
Sub addchart()
Dim myChtObj As ChartObject
Dim rngChtData As range
Dim rngChtXVal As range
Dim iColumn As Long
Dim intCol5 As Integer
n = 3 'a number of files opened
intCol5 = intCol5 + (n * 3 + 2) 'number of columns is count
If TypeName(Selection) <> "Range" Then Exit Sub
Set rngChtData = ("A2:A100") , ("2"): intCol5.Select
Set rngChtData = Selection
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart
.ChartType = xlXYScatterSmoothNoMarker
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iColumn = 2 To rngChtData.Columns(n).Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
intCol5 = intCol5 + 1
Next
myChtObj.Activate
End With
End Sub
Bookmarks