Transferring an array to multiple ranges

Question

I have become stuck trying to solve a piece of code I thought would be very simple. I have defined a range (2 rows, 150 columns) and transferred it to an array. I then want to use the defined array in multiple ranges (same size of 2 rows and 150 columns). I have written the following code:

Dim LocalArray As Variant
LocalArray = .Range("FD6781:KW6782").Value2
.Range("FD6839:KW6840,FD6955:KW6956,FD7013:KW7014,FD7071:KW7072").Value2 = LocalArray

The issue is that every second range defined in .range("FD6839:KW6840,FD6955:KW6956...") shows up as N/A. Hence range FD6839:KW6840 is correct while range FD6955:KW6956 is wrong. What have I done wrong in the above code?

Thank you!

Answer 1

You could loop. Otherwise, it does seem to be related to the number of columns causing the issue. Seems odd.

Option Explicit
Public Sub test()
    Dim localArray(), rng As Range, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet3")
    localArray = ws.Range("FD6781:KW6782").Value2

     For Each rng In ws.Range("FD6839, FD6955, FD7013, FD7071")
         rng.Resize(UBound(localArray, 1), UBound(localArray, 2)) = localArray
     Next

End Sub