How to offset a for loop?

[Waimea]

    Data = Sheets("Data").Range("Values2020").Value
   
    For i = 1 To UBound(Data)
   
         area = Data(i, 1)
         aval = Data(i, 2)
               
        myFunc area, aval
                
     Next i

First row is headers.

Column A contains names, column B to F contains values, named Values2020 to Values2025.

I am trying to offset Data somehow so that I can loop from column B to column F or column X...

I have tried with an outer loop:

Dim j As Integer

For j = 1 to 5
    Data = Sheets("Data").Range("Values202" & j).Value
Next j

Any suggestions?

 

[Norie]

Is that not working?

If it isn't how?

 

[Waimea]

Is that not working?

If it isn't how?

Hi Norie,

thank you for your reply.

The outer loop works if i copy column A and B and make them a range.

I then copy column A and C, column A and D etc.

But I want to have one column A with names and the other columns with data.

Is this possible?

 

[EXCEL MAX]

Try to multiply column, do something like this...

Dim area, aval, data, i
Dim varColumn

Sub OffSet()

    data = Sheets("Data").Range("Values2020").Value
    varColumn = 3
    For i = 1 To UBound(data)
         area = data(i, 1 * varColumn)
         aval = data(i, 1 * varColumn)    
        MsgBox (area & aval)    
     Next i

End Sub

 

[EXCEL MAX]

Sorry, I mean...

       area = data(i, 1 * varColumn)
       aval = data(i, 2 * varColumn)

 

[Waimea]

Hi EXCEL MAX,

thank you for your reply!

However, I get a subscript out of range with your updated code!

 

[Waimea]

I want to turn:

area = Data(i, 1)
aval = Data(i, 2)

Into:

area = Data(i, 1)
aval = Data(i, 3)

And then:

area = Data(i, 1)
aval = Data(i, 4)

etc.

 

[EXCEL MAX]

Try to set range ...........Sheets("Data").Range("A1:F5").Value
or set name for the range to "Values2020".

 

[Waimea]

Hi EXCEL MAX,

thank you for your reply!

How do you mean?

 

[Norie]

Why not put all the data in one array?

Data = Sheets("Data").Range("A1").CurrentRegion.Value

For idxCol= 2 To 6
    For idxRow = LBound(Data,1) To UBound(Data,1)
        area = Data(idxRow, 1)
        aval = Data(idxRow, idxCol)
        myFunc area, aval
    Next idxRow
Next idxCol