Copy Array to Multiple Range-CodePudding

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:

CodePudding user response：

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"

CodePudding user response：

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.