0

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:

enter image description here

This is what i hope to get:

enter image description here

Storax
  • 11,158
  • 3
  • 16
  • 33
Netz
  • 11
  • 2

2 Answers2

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"
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • 1
    Great Idea! Never thought of that. My project have 4 possible results. This works too. – Netz Dec 30 '21 at 08:57
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.

Storax
  • 11,158
  • 3
  • 16
  • 33
  • Is there a way to do without loops? – Netz Dec 30 '21 at 08:00
  • Does the proposed solution give you the result you want? – Storax Dec 30 '21 at 08:03
  • 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. – Netz Dec 30 '21 at 08:11
  • But you need to write to a non-contigous range, right? – Storax Dec 30 '21 at 08:13
  • Yes. The range have to be non-contigous. – Netz Dec 30 '21 at 08:16
  • Then you might try to add the code I provided in the link I added to the post. This will also tun off events which might slow down your code. But otherwise I do not know ho to write an array in one shot to non-contigous ranges – Storax Dec 30 '21 at 08:17
  • I tried the link. Thanks, Storax! It significantly reduced the time. – Netz Dec 30 '21 at 08:30