VBA - looping through columns with dynamic ranges

[Gnera]

Hi,

I have encountered the following problem:
I have a huge set of data (experimental measurements), which I have gathered to Excel so, that the data I'd like to plot in a graph is in every 8th column (first data series I'd like to plot is A30:B50, second I30:J80, third Q30:R70, and so on). I have managed to write a code that is working, but has each column in the code separately. Because I have to occasionally change the code, I'd like to write it using a loop so it'd be easier to modify. And that's where I seem to fail. This is my attempt:

'to loop through columns    
For i = ActiveSheet.Range("a30") To ActiveSheet.Range("eg30") Step 8 'if the first cell of the range is a certain value I want to exclude it from the graph
    For j = ActiveSheet.Range("f12").Column To ActiveSheet.Range("el12").Column Step 8 'the series names
    For k = ActiveSheet.Range("A30", Range("A30").End(xlDown)).Column To ActiveSheet.Range("eg30", Range("eg30").End(xlDown)).Column Step 8 'series x values if first cell of range is not excluded
    For l = ActiveSheet.Range("b30", Range("b30").End(xlDown)).Column To ActiveSheet.Range("eh30", Range("eh30").End(xlDown)).Column Step 8 'series y values if first cell of range is not excluded
    For m = ActiveSheet.Range("A31", Range("A31").End(xlDown)).Column To ActiveSheet.Range("eg31", Range("eg31").End(xlDown)).Column Step 8 'series x values if first cell of range is excluded
    For n = ActiveSheet.Range("b31", Range("b31").End(xlDown)).Column To ActiveSheet.Range("eh31", Range("eh31").End(xlDown)).Column Step 8 'series y values if first cell of range is excluded


'DATA SERIES
        'If first cell in the range equals 0.2, -0.2, 0.3 or -0.3 then I want to EXCLUDE that data pair.
        'In the code it looks as if I've written it the wrong way around 
        '(if first cell equals 0.2 fx, then the code says to include it in the range, although that's the opposite to what I want as an outcome), 
        'but the code does what I want --> makes no sense, I know, but somehow it works.
 
    If (i) = ("0.2") Or (i) = ("-0.2") Or (i) = ("0.3") Or (i) = ("-0.3") Then
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.FullSeriesCollection(1).Name = (j)
        ActiveChart.FullSeriesCollection(1).XValues = (k)
        ActiveChart.FullSeriesCollection(1).Values = (l)
    Else
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.FullSeriesCollection(1).Name = (j)
        ActiveChart.FullSeriesCollection(1).XValues = (m)
        ActiveChart.FullSeriesCollection(1).Values = (n)
        Exit For
    End If
   
    Next n
    Next m
    Next l
    Next k
    Next j
    Next i

The result of running this code is that I get a run-time error saying "A chart can only have up to 256 series" and the graph I get is filled with 256 empty series.

Any help would be appreciated!

 

[RobertSF]

I think the problem is that you are nesting your loops. That definitely generates more than 256 series since you are nesting six levels deep. Should each loop be independent from the next?

 

[Gnera]

Thank you for the reply! They should be independent, yes. But if in my code they are not then I don't know how to make them independent. I myself thought the mistake would probably be in how I've used the dynamic columns, because I have no idea how to insert them in the looping code nor did I find any examples someone doing it. By now I've understood I also have a mistake in my If-Then-Else, because regardless of the first cell it always chooses the Else option.

 

[bellman101]

Hi Gnera,

Looking at this, I think you actually only need a single loop, to process 8 columns at a time, I think the problem is also how you are using the For statements. Because you are using .Column, these variables will always be numeric, and as Range("A30") and Range("A31") are both column A, aka column 1, and same for B, in the code the variables k and m will always be the same - they'll both start on 1 and go up to 9, 17, 25, etc with each loop. variables l and n will do the same, but starting on 2, for column B.

If you use i as an integer, and then you can use Cells(row num, col num) to get the values. I'll show you what I mean:

so the For i line should look like this:

For i = 1 to 137 step 8

This will create 18 (I think?!) loops, where i will be 1, 9, 17, 25, etc.

So the whole For statement should look something like this:

For i = 1 to 137 step 8 ' i will be used throughout the loop to locate the required values
        If Abs(Cells(30, i)) = 0.2 or Abs(Cells(30, i)) = 0.3 Then    ' Abs returns positive from a negative or positive number
                ActiveChart.SeriesCollection.NewSeries
                ActiveChart.FullSeriesCollection(1).Name = Cells(12, i+5)    ' Cells() references a specific cell, set by the row number (12) and the column number (i+5)
                ActiveChart.FullSeriesCollection(1).XValues = Cells(30, i)     ' So on this one, for the first loop would be looking at row 30, column 1, so A30.  on loop 2, it would be row 30, column 9 - I30, and so on.
                ActiveChart.FullSeriesCollection(1).Values = Cells(30, i+1)   ' and this one at column 2, so B30, J30, etc.
        Else
                ActiveChart.SeriesCollection.NewSeries
                ActiveChart.FullSeriesCollection(1).Name = Cells(12, i+5)
                ActiveChart.FullSeriesCollection(1).XValues = Cells(31, I)
                ActiveChart.FullSeriesCollection(1).Values = Cells(31, I+1)
                Exit For
        End If
Next

As I say, this is all completely untested, but it should certainly get you much closer to what you need.

Cheers
JB

 

[Gnera]

Thanks, JB! I also thought about using Cells property instead of Range property and tried it yesterday later at night already, but then after reading your comment felt unbelievably stupid - I have no idea how did I not think of using only one variable... Anyways, I did some modifications using your advice (thanks a million, helped a lot!), but I don't seem to get the code to plot all the series. It only plots the first one and the graph has only one dataset.

'to loop through columns
   For i = 1 To 137 Step 8 'if i=1 --> column A
   j = Cells(30, i).End(xlDown).Row 'find the last used cell in a column to get the range length
        If Abs(Cells(30, i)) = 0.2 Or Abs(Cells(30, i)) = 0.3 Then    'depending on the value of the first cell in column it's either included in the range or not
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.FullSeriesCollection(1).Name = Cells(12, i + 5)  'when i=1 --> row 12, column 1+5=6 --> F12
            ActiveChart.FullSeriesCollection(1).XValues = Range(Cells(31, i), Cells(j, i)) 'series x values, when i=1 --> column 1 -> column A
            ActiveChart.FullSeriesCollection(1).Values = Range(Cells(31, i + 1), Cells(j, i + 1)) 'series y values, when i=1 --> column 1+1=2 --> column B
        Else
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.FullSeriesCollection(1).Name = Cells(12, i + 5)
            ActiveChart.FullSeriesCollection(1).XValues = Range(Cells(30, i), Cells(j, i))
            ActiveChart.FullSeriesCollection(1).Values = Range(Cells(30, i + 1), Cells(j, i + 1))
          Exit For
        End If
    Next i

However, when I delete Exit For then the graph has 18 data sets, but it plots only the last (18th) series, all the other datasets are empty.

Do you maybe have any suggestions?

Thanks again!
Gnera