vba - increment through 2 list at a time

Question

i'm trying to update a sheet depending on values in another sheet. I made 2 lists, with each one linked to the other. but when i increment one list is going to a cell +1 and the other one is incrementing occrectly.

Here is an exemple.

B16 = 'PL_GROWTH(CUSTOM)'!CN88/1000
C16 = 'PL_GROWTH(CUSTOM)'!CN99/1000
D16 = 'PL_GROWTH(CUSTOM)'!CN76/1000

here is my function:

Sub UpdateMGMT(ws As Worksheet)
    Dim sourceRangeGrowth As Range, targetRangeGrowth As Range, i As Integer, j As Integer
    
    ' Set up the source and target ranges
    Set sourceRangeGrowth = Sheets("PL_GROWTH(CUSTOM)").Range("CN88,CN99,CN76,CN110,CN191,CN191,CN253,CN67,CN307,CN307,CN94,CN105,CN82,CN116,CN192,CN192,CN254,CN308,CN308,CN95,CN106,CN83,CN117,CN193,CN193,CN255,CN309,CN309")
    Set targetRangeGrowth = ws.Range("B16,C16,D16,G16,H16,J16,O16,P16,Q16,S16,B18,C18,D18,G18,H18,J18,O18,Q18,S18,B19,C19,D19,G19,H19,J19,O19,Q19,S19")
    
    ' Copy data from source to target, handling division by zero errors
    j = 1
    For Each cell In sourceRangeGrowth
        Debug.Print "Copying data from cell " & cell.Address(False, False) & " (value: " & cell.Value & ") to cell " & targetRangeGrowth(j).Address(False, False)
        If cell.Value = 0 Then
            targetRangeGrowth(j).Value = 0
            Debug.Print "Value copied: 0"
        Else
            targetRangeGrowth(j).Value = cell.Value / 1000
            Debug.Print "Value copied: " & targetRangeGrowth(j).Value
        End If
        j = j + 1
    Next cell
End Sub

here are some logs

Copying data from cell CN88 (value: 147905.714482528) to cell B16 Value copied: 147.905714482528

Copying data from cell CN99 (value: 111501.254016756) to cell B17 Value copied: 111.501254016756

It should be C16 and not B17

score 2 · Accepted Answer · answered Apr 28 '23 at 13:53

Try changing targetRangeGrowth(j).Select to targetRangeGrowth.Areas(j).Select. Since the cells in the target range are specified individually, Excel treats them as separate areas.

Sub UpdateMGMT(ws As Worksheet)
    Dim sourceRangeGrowth As Range, targetRangeGrowth As Range, i As Integer, j As Integer
    
    ' Set up the source and target ranges
    Set sourceRangeGrowth = Sheets("PL_GROWTH(CUSTOM)").Range("CN88,CN99,CN76,CN110,CN191,CN191,CN253,CN67,CN307,CN307,CN94,CN105,CN82,CN116,CN192,CN192,CN254,CN308,CN308,CN95,CN106,CN83,CN117,CN193,CN193,CN255,CN309,CN309")
    Set targetRangeGrowth = ws.Range("B16,C16,D16,G16,H16,J16,O16,P16,Q16,S16,B18,C18,D18,G18,H18,J18,O18,Q18,S18,B19,C19,D19,G19,H19,J19,O19,Q19,S19")
    
    ' Copy data from source to target, handling division by zero errors
    j = 1
    For Each cell In sourceRangeGrowth
        Debug.Print "Copying data from cell " & cell.Address(False, False) & " (value: " & cell.Value & ") to cell " & targetRangeGrowth.Areas(j).Address(False, False)
        If cell.Value = 0 Then
            targetRangeGrowth.Areas(j).Value = 0
            Debug.Print "Value copied: 0"
        Else
            targetRangeGrowth.Areas(j).Value = cell.Value / 1000
            Debug.Print "Value copied: " & targetRangeGrowth.Areas(j).Value
        End If
        j = j + 1
    Next cell
End Sub

Note that you should also Dim all variables, such as Dim cell as Range.

score 1 · Answer 2 · answered Apr 28 '23 at 13:53

Your issue is that a range defined by comma-separated cell addresses creates an Area for each value. The following code, for example, would output $B$1:

Set rng = Sheet1.Range("A1,B1")
Debug.Print rng.Areas(2).Address

In your code, the iterator goes beyond the end of a range. In these cases the cell in the next row down is returned. So the following code would return $A$2 (which is the problem your code is experiencing):

Set rng = Sheet1.Range("A1,B1")
Debug.Print rng(2).Address

You need to revise this line to include the area object:

targetRangeGrowth.Areas(j).Value = cell.Value / 1000

The reason that your For Each loop still works is that it iterates the cells in the Areas.

score 0 · Answer 3 · answered Apr 28 '23 at 14:02

What you are doing is going to the next row as you could see in the VBE when typing targetRangeGrowth( it should show _Default([RowIndex],[ColumnIndex]) so your "j" is just going to the next row as it would in a contiguous range.

Here are some examples on how to loop over non-contiguous ranges but already being in a loop and having to go hand in hand with said loop, you could work with an array instead (since you're hardcoding it anyway).

Here's with looping through the cells:

Sub UpdateMGMT(ws As Worksheet)
    Dim sourceRangeGrowth As Range, targetRangeGrowth As Range, i As Integer, j As Integer
    Dim arr()
    
    ' Set up the source and target ranges
    Set sourceRangeGrowth = Sheets("PL_GROWTH(CUSTOM)").Range("CN88,CN99,CN76,CN110,CN191,CN191,CN253,CN67,CN307,CN307,CN94,CN105,CN82,CN116,CN192,CN192,CN254,CN308,CN308,CN95,CN106,CN83,CN117,CN193,CN193,CN255,CN309,CN309")
    arr = Array("B16", "C16", "D16", "G16", "H16", "J16", "O16", "P16", "Q16", "S16", "B18", "C18", "D18", "G18", "H18", "J18", "O18", "Q18", "S18", "B19", "C19", "D19", "G19", "H19", "J19", "O19", "Q19", "S19")
    
    ' Copy data from source to target, handling division by zero errors
    j = 0 'arrays like this start at index 0
    For Each cell In sourceRangeGrowth
        Debug.Print "Copying data from cell " & cell.Address(False, False) & " (value: " & cell.Value & ") to cell " & arr(j)
        If cell.Value = 0 Then
            ws.Range(arr(j)).Value = 0
            Debug.Print "Value copied: 0"
        Else
            ws.Range(arr(j)).Value = cell.Value / 1000
            Debug.Print "Value copied: " & ws.Range(arr(j)).Value
        End If
        j = j + 1
    Next cell
End Sub

But since they're normally the same length, you might as well do it on both like so:

Sub UpdateMGMT_V2(ws As Worksheet) 'notice I changed the name here to have a difference
    Dim j As Integer
    Dim arr1(), arr2(), wsPL As Worksheet
    
    Set wsPL = Sheets("PL_GROWTH(CUSTOM)")
    ' Set up the source and target ranges
    arr1 = Array("CN88", "CN99", "CN76", "CN110", "CN191", "CN191", "CN253", "CN67", "CN307", "CN307", "CN94", "CN105", "CN82", "CN116", "CN192", "CN192", "CN254", "CN308", "CN308", "CN95", "CN106", "CN83", "CN117", "CN193", "CN193", "CN255", "CN309", "CN309")
    arr2 = Array("B16", "C16", "D16", "G16", "H16", "J16", "O16", "P16", "Q16", "S16", "B18", "C18", "D18", "G18", "H18", "J18", "O18", "Q18", "S18", "B19", "C19", "D19", "G19", "H19", "J19", "O19", "Q19", "S19")
    
    ' Copy data from source to target, handling division by zero errors
    '0/1000 is no issue, 1000/0 is, you're not dividing by zero in your example, just dividing 0
    'but I'm keeping your structure in case you need it as such
    For j = 0 To UBound(arr1)
        Debug.Print "Copying data from cell " & arr1(j) & " (value: " & wsPL.Range(arr1(j)) & ") to cell " & arr2(j)
        If wsPL.Range(arr1(j)).Value = 0 Then
            ws.Range(arr2(j)).Value = 0
            Debug.Print "Value copied: 0"
        Else
            ws.Range(arr2(j)).Value = wsPL(arr1(j)).Value / 1000
            Debug.Print "Value copied: " & ws.Range(arr2(j)).Value
        End If
    Next j
End Sub

You could also place the values in different arrays to have even less interactions with Excel but this should be understandable/doable enough. If you'd be dealing with more cells, it'd be a hassle to keep hardcoding like this anyway.

Feel free to ask questions :)

vba - increment through 2 list at a time

3 Answers3