Capture every number in the range and populate the field

[motilulla]

Using Excel 2010
Hello,

I need VBA which find the every numbers is in the range (from the 1 to 35 numbers). Check it within the rows 5 into 5.

Here is an example…

1st starting ranges C6:G10, (list every number from 1 to 35 numbers) in the Cell J10 to right under the header number in the range J10:AR10

2nd starting ranges C7:G11, (list every number from 1 to 35 numbers) in the Cell J11 to right under the header number in the range J11:AR11

3rd starting ranges C8:G12, (list every number from 1 to 35 numbers) in the Cell J12 to right under the header number in the range J12:AR12

And so on till end of the data……

Example sheet attached

Find Unique.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1
2
3
4
5			n1	n2	n3	n4	n5			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35
6			2	7	28	22	13
7			24	20	11	23	12
8			11	18	28	1	29
9			9	6	9	17	8
10			22	11	24	7	26	1st Capture Every Number In The Range C6:G10-->		1	2				6	7	8	9		11	12	13				17	18		20		22	23	24		26		28	29
11			20	21	9	12	20	2nd Capture Every Number In The Range C7:G11-->		1					6	7	8	9		11	12					17	18		20	21	22	23	24		26		28	29
12			30	16	25	14	4	3rd Capture Every Number In The Range C8:G12-->		1			4		9	7	8	9		11	12	14			16	17	18		20	21	22		24	25	26		28	29	30
13			4	17	19	30	9			?
14			22	5	16	25	18			?
15			21	23	21	25	35			?
16			2	12	14	12	20			?
17			7	22	23	29	18			?
18			17	13	4	22	21			?
19			4	9	17	22	5			?
20			2	9	13	26	35			?
21			9	17	7	3	6
22
23
List Every Number In Range

Regards,
Moti

 

[hagia_sofia]

Hello, if you are not insisting on VBA you could try:

=IF(COUNTIF($C6:$G10,J$5)>0,J$5,"")

 

[kvsrinivasamurthy]

Try.

Sub CaptureNumbers()
Dim Rng As Range
Dim Lr&, T&, rng1$
Dim M

Lr = Range("C" & Rows.Count).End(xlUp).Row
Set Rng = Range("C6:G10")
For T = 10 To Lr
rng1 = Rng.Offset(T - 10, 0).Address
M = Evaluate("Transpose(if(countif(" & rng1 & ",Row(1:35))>0,Row(1:35),""""))")
Range("J" & T).Resize(1, 35) = M: M = ""
Next T

End Sub

 

[motilulla]

Hello, if you are not insisting on VBA you could try:

=IF(COUNTIF($C6:$G10,J$5)>0,J$5,"")

hagia_sofia, I appreciate help; I like your formula option. It worked fine.

Wishing you a great weekend! And Good Luck.

Kind regards,
Moti

 

[motilulla]

Try.

Sub CaptureNumbers()
Dim Rng As Range
Dim Lr&, T&, rng1$
Dim M

Lr = Range("C" & Rows.Count).End(xlUp).Row
Set Rng = Range("C6:G10")
For T = 10 To Lr
rng1 = Rng.Offset(T - 10, 0).Address
M = Evaluate("Transpose(if(countif(" & rng1 & ",Row(1:35))>0,Row(1:35),""""))")
Range("J" & T).Resize(1, 35) = M: M = ""
Next T

End Sub

kvsrinivasamurthy, I appreciate you help, thank you for the VBA, it worked Perfect

Wishing you a great weekend! And Good Luck.

Kind regards,
Moti