Copy Array to Multiple Range

Question

My first time posting here. I am trying to copy an Array with 10 elements to 10 separate ranges. The problem is that all the cells only have the 1st Element in the array. In the example below, I only get ONs. I don't get OFFs in the 3rd to 5th ranges. Edit I'm trying to do this without using loops because I might have many elements and it slows down during copy.

Dim Rng As String
Dim OnOff() As Variant
    
Rng = "F7,G4,H3,I9,J8,K2,L10,M1,N6,O5"
OnOff = Array("ON", "ON", "OFF", "OFF", "OFF", "ON", "ON", "ON", "ON", "ON")
    
Range(Rng).Value = OnOff

This is what i am getting:

This is what i hope to get:

VBasic2008 · Accepted Answer · 2021-12-30 08:51:48Z

1

Write to Non-Contiguous Ranges

Try creating two separate strings of cell addresses.

Const rgOnAddress As String = "F7,G4,L10,M1,N6,O5"
Const rgOffAddress As String = "H3,I9,J8"

Range(rgOnAddress).Value = "ON"
Range(rgOffAddress).Value = "OFF"

answered Dec 30, 2021 at 8:51

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

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

1 Comment

Netz Over a year ago

Great Idea! Never thought of that. My project have 4 possible results. This works too.

Storax · Accepted Answer · 2021-12-30 08:22:37Z

0

According to your posted images you just want to loop through Rng and OnOff simultanoeusly

Dim Rng As String
Dim OnOff() As Variant

Rng = "F7,G4,H3,I9,J8,K2,L10,M1,N6,O5"
OnOff = Array("ON", "ON", "OFF", "OFF", "OFF", "ON", "ON", "ON", "ON", "ON")

Dim i As Long
Dim v As Variant
v = Split(Rng, ",")
For i = LBound(OnOff) To UBound(OnOff)
    Range(v(i)) = OnOff(i)
Next i

As there seems the need to write to non-contiguous ranges one could use the code in this link to speed up.

edited Dec 30, 2021 at 8:22

answered Dec 30, 2021 at 7:58

Storax

12.7k3 gold badges21 silver badges36 bronze badges

7 Comments

Netz Over a year ago

Is there a way to do without loops?

Storax Over a year ago

Does the proposed solution give you the result you want?

Netz Over a year ago

Yes it does and thank you for that. But my initial intent was to speed up the time to copy the data, as i have copy hundreds of it. I also tried Application.Calculation = xlCalculationManual and Application.ScreenUpdating = False but it is still slow.

Storax Over a year ago

But you need to write to a non-contigous range, right?

Netz Over a year ago

Yes. The range have to be non-contigous.

|

Collectives™ on Stack Overflow

Copy Array to Multiple Range

2 Answers 2

Write to Non-Contiguous Ranges

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Write to Non-Contiguous Ranges

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related