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!

Have you tried assigning the ranges separately, rather than attempting all four at once? This is the first step I would take. — Andy G
– Andy G, Commented Nov 1, 2018 at 13:01
Yes, it works when doing them separately. I was just trying to limit the amount of code, and then I don't understand why above wouldn't work. — Philip
– Philip, Commented Nov 1, 2018 at 13:08
@Philip Good question. Solution may be looping.But real question is why it is behaving like this? — Ahmed AU
– Ahmed AU, Commented Nov 1, 2018 at 13:20
I tried it with 2 Col X 5 Row data. it is behaving weird only in 2nd, 4th ranges. if 2nd and 4th ranges are kept 5 col X 2 row, data is being weirdly restructured in 2nd and 4th ranges. .Range("A11:B15,A21:E22,A31:B35,A41:E42").Value = .Range("A1:B5").Value. However if square shaped ranges (i.e. rows=col) used the syntax is working correctly (I tired up to 8th range with 5X5 range) — Ahmed AU
– Ahmed AU, Commented Nov 1, 2018 at 14:12

QHarr · Accepted Answer · 2018-11-01 13:42:56Z

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

edited Nov 1, 2018 at 13:42

answered Nov 1, 2018 at 13:05

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BigBen Over a year ago

Completely agree with dropping the intermediary array. Also, at least with sample data, there was no need for Union: ws.Range("D1:E2,G1:H2,J1:K2") = arr.

Philip Over a year ago

Hi both, when using that method I get the same problem as previously (i.e. every second range is wrong).

Philip Over a year ago

Just tried with the updated code QHarr, that works perfectly! :) Thank you!

John Alexiou Over a year ago

I recommend using rng.Resize(UBound(localArray, 1), UBound(localArray, 2)).Value = localArray instead of the implicit (with no .Value) in order to make it clear you are transfering values and not assigning references.

Collectives™ on Stack Overflow

Transferring an array to multiple ranges

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related