Vba code in MS Excel 2007
Sub Chart()
Dim ch As Chart
Set ch = ActiveWorkbook.Charts.Add
Set ch = ch.Location(where:=xlLocationAsObject, Name:="Sheet1")
With ch
.ChartType = xlXYScatterSmooth
End With
Dim k As Integer
'k = 1
Dim m, n As Integer
For i = 11 To 11000
If Worksheets("Sheet1").Cells(i, 10 + c).Value = 0.01 Then
j = j + 1
' MsgBox ("i: " + i + "j: " + j)
For k = 1 To 1000
If Worksheets("Sheet1").Cells(i + k, 10 + c).Value > 0.01 Then
m = i
n = i + k - 1
'MsgBox ("m: " + "m" + " n: " + "n")
Else
' MsgBox (i)
' ch.Select
' ch.Activate
ch.SeriesCollection.NewSeries
ch.SeriesCollection(j).Name = " =""Series"""
ch.SeriesCollection(j).XValues = "='Sheet1'!$L$m:$L$n"
ch.SeriesCollection(j).Values = "='Sheet1'!$D$m:$D$n"
'j = 0
'R = R + 1
'Exit For
End If
Next k
Else
End If
'i = i + 19
Next
End Sub
============== I have a spreadsheet and I am trying to plot a one series after another based on a logic I have used with some 'For' loops. I am not sure why, but the graph does NOT get populated as per the given columns L and D. I would appreciate any help on this. Also,I get a 400 and a 1004 error some times.
RE: VBA code...
Hello,
Sorry that I say this, but I think your code and explanation below it is pretty mess. Can you be more specific about your logic of what exactly you want to do, or post some example worksheet, or both.
However, take notice of this:
1) In your code lines
ch.SeriesCollection(j).XValues = "='Sheet1'!$L$m:$L$n"
ch.SeriesCollection(j).Values = "='Sheet1'!$D$m:$D$n"
I think must be written like that
ch.SeriesCollection(j).XValues = "='Sheet1'!$L$" & m & ":$L$" & n
ch.SeriesCollection(j).Values = "='Sheet1'!$D$" & m & ":$D$" & n
or
ch.SeriesCollection(j).XValues = Worksheets("Sheet1").Range("$L$" & m & ":$L$" & n)
ch.SeriesCollection(j).Values = Worksheets("Sheet1").Range("$D$" & m & ":$D$" & n)
because I suppose your aim is to use dynamic range for every new Series. But this not resolve your problems. From here they start...
2) About your logic.
First - what is the purpose of variable c. You do nothing with it.
Second - I think your counters j, m and n are not on correct place, but without more information of what you want to do it is merely guessing. However, in the way you use them - see 1) from my post - you create bunch of series with the same range of values and they overlap one another. The problem with j is that you not check it before create new series and if you exceed the limit of 256 series in one chart you get run-time error.
Third - I think to stop here because with my guessing I not help you. But if you explain your problem more clearly I will try to help you if I can.
Sorry for my English. I hope it is not too annoying to read.
Best regards.
I got it working. thanks. I
I got it working. thanks. I was missing the "& m" in the code line.
Variable c is a constant
Variable c is a constant which is globally defined. m and n are in the correct place and it works perfect now.
However, thanks for your code lines., for some reason I did not put the "& m" and hence was getting error. Lastly your English is not bad :)