Loop - Reference Cells Correctly?

[Janc]

Hi folks

I've been working on trying to create a loop for adding a graph to a new worksheet for several rows of data and I've got so far with it but hit a brick wall with referencing the correct row and I can't seem to get my head around what is bound to be a simple thing.

This is my code with graph formatting etc removed:

Sub graphs()
'
' graphs Macro
' 13/02/2015

    
    Dim n As Integer      
    For n = 5 To 100
    If Sheets("Data").Cells(n, 1) = 0 Then Exit For
  
    Sheets.Add
    ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = Worksheets("data").Cells(n, 1).Value
    
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries

'Adds static data series:
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C2:R3C3"
    ActiveChart.SeriesCollection(1).Values = "=Data!R4C2:R4C3"
    ActiveChart.SeriesCollection(1).Name = "=Data!R4C1"
    
'Adds Individual data series:
    ActiveChart.SeriesCollection(2).Values = "=Data!R5C2:R5C3"
    ActiveChart.SeriesCollection(2).Name = Sheets("Data").Cells(n, 1)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=Worksheets("data").Cells(n, 1).Value
      
        
       Next n

End Sub

I have got the name of the series(2) data referencing correctly, but I'm unsure how to code the values of this series.

[GC Excel]

Hello Janc,

If I understood well, change this part of the code :

        'Adds Individual data series:
        ActiveChart.SeriesCollection(2).Values = Sheets("Data").Cells(n, 2).Resize(1, 2) '          "=Data!R5C2:R5C3"
        ActiveChart.SeriesCollection(2).Name = Sheets("Data").Cells(n, 1)
        ActiveChart.Location Where:=xlLocationAsObject, Name:=Worksheets("data").Cells(n, 1).Value

[Janc]

Thanks so much, I really appreciate the explanation as well as the code help as that will (should!) help me learn more for progressing this project.

[Janc]

Thank you so much, that works perfectly

Could you explain the logic behind this part: Cells(n, 2).Resize(1, 2) so I can understand where I'll need to make changes to include further columns in my series, and also so I can add an additional new chart on each sheet from a later column.

[GC Excel]

Your previous code was adding the info from row 5 only for the 2nd data series

ActiveChart.SeriesCollection(2).Values = "=Data!R5C2:R5C3"

My piece of code takes the values starting from the cells in row n column B (.cells(n,2)) and extends the range by 2 columns (.Resize(1,2))

ActiveChart.SeriesCollection(2).Values = Sheets("Data").Cells(n, 2).Resize(1, 2)

The trick here is the .Resize(number of row, number of columns) function.
I'm starting from Cell(n, 2) and extends it 2 columns.
So if we are in B5, the final selection is B5:C5

Check the Help in Excel on those 2 functions : OFFSET and RESIZE. They are very useful when it's time to select range.