Need VBA Help! For join the numbers in-group separating by vertical bar.

[motilulla]

Hello,

My result data range D10:I22 with 6 numbers in each row, this result I want to put in the 7 group of 7 numbers...1 to 7, 8 to 14, 15 to 21 and so on separating by the vertical bar in the range M6:S21 as shown in the data layout below and for the more detail information the image is attached.

Please need VBA help?

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1													GR1	GR2	GR3	GR4	GR5	GR6	GR7
2													1	8	15	22	29	36	43
3													2	9	16	23	30	37	44
4													3	10	17	24	31	38	45
5													4	11	18	25	32	39	46
6													5	12	19	26	33	40	47
7													6	13	20	27	34	41	48
8													7	14	21	28	35	42	49
9			Date	n1	n2	n3	n4	n5	n6	Sum			Join	Join	Join	Join	Join	Join	Join
10			15-07-05	10	18	26	35	41	48	178				10		26	34	41	48
11			18-09-09	6	8	12	30	40	41	137			6	8	12		30	40|41
12			13-09-12	14	19	20	23	24	28	128				14	19|20	23|24|28
13			15-06-14	1	6	13	21	26	40	107			1|6	13	21	26		40
14			12-04-15	8	15	19	23	27	33	125			8		15|19	23|27	33
15			07-08-15	2	26	32	37	40	43	180			2			26	32	37|40	43
16			25-11-15	1	4	6	7	42	43	103			1|4|6|7					42	43
17			03-04-18	15	23	29	33	37	41	178					15	23	29|33	37|41
18			04-06-18	1	14	21	26	35	49	146			1	14	21	26	35		49
19			09-09-18	4	7	10	18	27	31	97			4|7	10	18	27	31
20			05-02-21	3	14	23	29	35	48	152			3	14		23	29|35		48
21			09-05-21	3	8	19	20	44	45	139			3	8	19|20				44|45
22			15-07-21	15	17	19	21	32	35	139					18|17|19|21		32|35
23
24
25
26

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

How about

Sub motilulla()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nc As Long
   
   Ary = Range("D10:I" & Range("D" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 7)
   For r = 1 To UBound(Ary)
      For c = 1 To 7
         nc = Int((Ary(r, c) - 1) / 7) + 1
         If Nary(r, nc) = "" Then Nary(r, nc) = Ary(r, c) Else Nary(r, nc) = Nary(r, nc) & "|" & Ary(r, c)
      Next c
   Next r
   Range("M10").Resize(r - 1, 7).Value = Nary
End Sub

 

[motilulla]

How about

Sub motilulla()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nc As Long
  
   Ary = Range("D10:I" & Range("D" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 7)
   For r = 1 To UBound(Ary)
      For c = 1 To 7
         nc = Int((Ary(r, c) - 1) / 7) + 1
         If Nary(r, nc) = "" Then Nary(r, nc) = Ary(r, c) Else Nary(r, nc) = Nary(r, nc) & "|" & Ary(r, c)
      Next c
   Next r
   Range("M10").Resize(r - 1, 7).Value = Nary
End Sub

Fluff, I like the macro it worked even without the header numbers in range M2:S8 result absently perfect, as I require.

Thank you so much for your help and time you took for building a macro solution.

Just a note after I run the code it stops at the line Below
"If Nary(r, nc) = "" Then"

After changing line "For c = 1 To 7"

To this "For c = 1 To 6" run normally

Good Luck have a good day

Kind Regards,
Moti

 

[Fluff]

Glad to help & thanks for the feedback.