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?

 

[Waimea]

Hi Norie,

thank you for your reply. Your code works great!

 

[Waimea]

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

Hi again,

I have data for 5 years, 2020 to 2025.

Where would I start if I wanted to find the highest change between years, ex. 2021 value - 2020 value, and 2025-2020 values?

I am using your code but I don't know, at the moment, how to proceed with it.

 

[Norie]

Would that be the highest change between years for each row, or some other metric?

Perhaps you could post some sample data with expected results.

 

[Waimea]

Hi Norie,

thank you for your reply!

I started a new thread at How to analyze an array year to year to find the highest change in actual numbers?

 

[Waimea]

Would that be the highest change between years for each row, or some other metric?

Perhaps you could post some sample data with expected results.

Hi,

could you help me with how to append your code with more code?

I want to calculate the difference between years 2020 and 2021, 2021 and 2022, 2022 and 2023, 2023 and 2024, 2024 and 2025 and finally the biggest difference between 2020 and 2025?

 

[Waimea]

I am trying to use two arrays and I want to compare the two, or more, arrays?

 

[Norie]

Why 2 arrays?

 

[Waimea]

Hi Norie,

thank you for your reply.

First array is for data from 2015 to 2020. Second array is from 2020 to 2025.

I want to find the max value for each row between 2015 and 2020 and then the max value for each row between 2020 and 2025.

After I have the two arrays, I want to compare them and se if for example if the absolute change is smaller or bigger in 2015 to 2020 or 2020 to 2025.

Can you help me?

 

[Norie]

How about a function that will return a single column array with the maximum value for each row of the array passed to it?

Function GetMaxPerRow(arr As Variant) As Variant
Dim arrMax() As Variant
Dim valMax As Double
Dim idxCol As Long
Dim idxRow As Long

    ReDim arrMax(1 To UBound(arr,1), 1 To 1)

    For idxRow = 1 To UBound(arr,1)
        valMax = 0
        For idxCol = 1 To UBound(arr,2)
            If arr(idxRow, idxCol) > valMax Then
                valMax = arr(idxRow, idxCol)
            End If
        Next idxCol
        arrMax(idxRow, 1) = valMax
    Next idxRow

    GetMaxPerRow = arrMax

End Function

 

[Waimea]

Hi Norie,

thank you for your reply!

Would it be possible to convert the function into a subroutine?

Data = Sheets("Data").Range("CH26").CurrentRegion.Value
Data2 = Sheets("Data").Range("CV26").CurrentRegion.Value

muncipality = Data(myRow, 1)
myValue = Data(myRow, myCol)

So that I would write?

Call GetMaxPerRow(Data)
Call GetMaxPerRow(Data2)

Or am I missing something with it being a function instead of a subroutine?

When I wrote max per row, I am trying to find the max value of 2016-2015, 2017-2016, 2018-2017 etc for each row.

Town	2015	2016	2017
Town1	500	495	510