vba – increment through 2 list at a time

April 28, 2023

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

>Solution :

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.