Offset selection by 3 columns?

[JKB]

Hi everybody!

I have a vector (in one row) containing the following data: 1 2 3 4.... 369.

I would like to distribute it in a row which is 3 times as long, i.e. do the following:

1 [empty cell] [empty cell] 2 [empty cell] [empty cell] 3..... [empty cell] [empty cell] 369.


I first thought that a smart way of doing this would be through VBA and use the cut/paste tool, but i thought it got a bit complicated hence i wanted to ask you guys, whether you know a smart way of doing it?

Hope someone can help!

[p45cal]

If it really is 1,2,3 (sequential numbers at least) then do it manually as follows:
put the starting number (1) in one cell. Leave 2 blank cells to the right and put the next sequential number (2) in the 3rd cell.
Now select the following the 6 cells starting with the 1 and going 2 cells beyond the two so you have selected:
1,blank,blank,2,blank,blank.
Now grab the fill handle at bottom right of this selection and drag as far as you need, then let go. QED.

Tweaking a recorded macro gives:

Sub blah()
Set Startcell = ActiveCell
Startcell.Value = 1 'you can leave this and the line below out if you want to seed the first 2 numbers manually.
Startcell.Offset(, 3).Value = 2
Startcell.Resize(, 6).AutoFill Destination:=Startcell.Resize(, 369 * 3)
End Sub

where you select the starting cell before you run the macro.

Now if you want to redistribute an existing sequence of numbers which aren't necessarily in sequence then that's another thing:
With this macro, first select the entire set of numbers you want spaced out then run:

Sub blah2()
origValues = Selection.Value
Selection.ClearContents
ofset = 0
For Each v In origValues
  Selection.Cells(1).Offset(, ofset).Value = v
  ofset = ofset + 3
Next v
End Sub

[JKB]

The values are not 1 2 3 4 it was just to illustrate! Given the data, i tried to use you second suggestion, but im not sure im quite following. I tried using this, but it doesnt like it one bit.

origValues = ActiveSheet.Range("H1", Range("H1").End(xlToRight)).Value (this gives me the 369 cells in a range, as far as i know!) but it doesnt seem to work! How would you define origValues?

Thanks for the help so far!

If it really is 1,2,3 (sequential numbers at least) then do it manually as follows:
put the starting number (1) in one cell. Leave 2 blank cells to the right and put the next sequential number (2) in the 3rd cell.
Now select the following the 6 cells starting with the 1 and going 2 cells beyond the two so you have selected:
1,blank,blank,2,blank,blank.
Now grab the fill handle at bottom right of this selection and drag as far as you need, then let go. QED.

Tweaking a recorded macro gives:

Sub blah()
Set Startcell = ActiveCell
Startcell.Value = 1 'you can leave this and the line below out if you want to seed the first 2 numbers manually.
Startcell.Offset(, 3).Value = 2
Startcell.Resize(, 6).AutoFill Destination:=Startcell.Resize(, 369 * 3)
End Sub

where you select the starting cell before you run the macro.

Now if you want to redistribute an existing sequence of numbers which aren't necessarily in sequence then that's another thing:
With this macro, first select the entire set of numbers you want spaced out then run:

Sub blah2()
origValues = Selection.Value
Selection.ClearContents
ofset = 0
For Each v In origValues
  Selection.Cells(1).Offset(, ofset).Value = v
  ofset = ofset + 3
Next v
End Sub

[JKB]

Okay i found the solution inspired p45cal! This code take "mYselection" and distributes it with n = 3, i.e. 2 spaces. Hope somebody else can use this later

Sub distributor()
mySelection = ActiveSheet.Range("D3", Range("D3").End(xlToRight))
ActiveCell = Range("D4").Select
n = 3
For Each i In mySelection
    ActiveCell = i
    ActiveCell.Offset(0, n).Select
Next i
End Sub

[p45cal]

in that case

Sub distributor()
mySelection = ActiveSheet.Range("D3", Range("D3").End(xlToRight))
For Each i In mySelection
  Range("D4").Offset(, n).Value = i
  n = n + 3
Next i
End Sub