Switch formula control system to VBA code

[Aretradeser]

On Sheet1 I have a series of data. In column 2, there are name records; and, from column 3 to column 14, titled with the months of the year, with number records.
On Sheet2, in row 4, from column 10 to 21, I have a formula that detects which name has a zero record and records it in the corresponding month column on Sheet2.

=IFERROR(INDEX(Sheet1!$B$3:$B$25;AGGREGATE(15;6;(ROW(Sheet1!$B$3:$B$25)-ROW(Sheet1!$B$3)+1)/(Sheet1!C$3:C$25=0);ROWS(J$4:J4)));"")

However, I see a small problem with the manual application of this system.
Currently, I have about 1200 records, which will increase; and, with this formula application system, I have to drag them until I reach the last row of the column in which there are no more records that meet this condition.
My question is the following:
Could it be done with VBA code and execute it with a button, that would do the same; and that, in addition, would register the new names that meet this condition?

Libro1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	NAMES	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
3	Name1	2	3	0	3	5	7	1	0	2	3	0	10
4	Name2	2	2	10	6	14	0	12	3	0	0	9	10
5	Name3	2	4	5	6	0	4	0	10	6	2	4	0
6	Name4	2	1	4	1	1	14	8	7	13	0	6	0
7	Name5	2	2	7	3	6	11	4	0	14	14	1	14
8	Name6	1	3	5	7	3	14	0	0	11	14	0	4
9	Name7	2	2	7	8	10	12	1	12	0	5	13	8
10	Name8	2	3	3	0	4	0	1	4	6	0	0	0
11	Name9	2	4	0	0	13	11	9	9	10	3	9	5
12	Name10	0	4	3	10	10	0	13	1	8	9	9	3
13	Name11	2	0	5	0	6	8	0	0	8	5	3	4
14	Name12	0	2	3	1	3	10	8	13	6	7	10	0
15	Name13	0	1	9	7	14	12	10	0	6	7	0	4
16	Name14	1	0	4	9	0	13	0	5	0	0	2	4
17	Name15	1	0	9	7	13	0	0	12	10	13	0	3
18	Name16	2	3	8	9	11	6	1	0	0	10	0	0
19	Name17	0	9	0	1	9	12	11	14	7	12	0	0
20	Name18	0	5	9	4	7	10	4	3	11	4	9	13
21	Name19	1	0	0	5	4	7	7	0	0	0	0	1
22	Name20	0	6	1	2	11	3	0	3	2	11	3	5
23	Name21	6	2	1	7	0	6	6	0	1	0	0	8
24	Name22	0	6	7	8	10	0	0	1	0	0	3	7
25	Name23	2	8	1	3	5	3	4	10	7	0	10	0
Hoja1

Libro1.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U
3	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
4	Name10	Name11	Name1	Name8	Name3	Name2	Name3	Name1	Name2	Name2	Name1	Name3
5	Name12	Name14	Name9	Name9	Name14	Name8	Name6	Name5	Name7	Name4	Name6	Name4
6	Name13	Name15	Name17	Name11	Name21	Name10	Name11	Name6	Name14	Name8	Name8	Name8
7	Name17	Name19	Name19			Name15	Name14	Name11	Name16	Name14	Name13	Name12
8	Name18					Name22	Name15	Name13	Name19	Name19	Name15	Name16
9	Name20						Name20	Name16	Name22	Name21	Name16	Name17
10	Name22						Name22	Name19		Name22	Name17	Name23
11								Name21		Name23	Name19
12											Name21
Hoja2

Cell Formulas
Range	Formula
J4:U12	J4	=IFERROR(INDEX(Hoja1!$B$3:$B$25,AGGREGATE(15,6,(ROW(Hoja1!$B$3:$B$25)-ROW(Hoja1!$B$3)+1)/(Hoja1!C$3:C$25=0),ROWS(J$4:J4))),"")

 

[Fluff]

Are you asking if the whole thing can be done with a macro, or if a macro can insert the formula?

 

[Aretradeser]

Are you asking if the whole thing can be done with a macro, or if a macro can insert the formula?

If everything can be done with a macro

 

[Fluff]

Ok, how about

Sub Aretradeser()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, Mx As Long
   
   With Sheets("Hoja1")
      Ary = .Range("B3:N" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2) - 1)
   
   For c = 2 To UBound(Ary, 2)
      nr = 0
      For r = 1 To UBound(Ary)
         If Ary(r, c) = 0 Then
            nr = nr + 1
            Nary(nr, c - 1) = Ary(r, 1)
            If nr > Mx Then Mx = nr
         End If
      Next r
   Next c
   Sheets("Hoja2").Range("J4").Resize(Mx, UBound(Nary, 2)).Value = Nary
End Sub

 

[Aretradeser]

Ok, how about

Sub Aretradeser()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, Mx As Long
  
   With Sheets("Hoja1")
      Ary = .Range("B3:N" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2) - 1)
  
   For c = 2 To UBound(Ary, 2)
      nr = 0
      For r = 1 To UBound(Ary)
         If Ary(r, c) = 0 Then
            nr = nr + 1
            Nary(nr, c - 1) = Ary(r, 1)
            If nr > Mx Then Mx = nr
         End If
      Next r
   Next c
   Sheets("Hoja2").Range("J4").Resize(Mx, UBound(Nary, 2)).Value = Nary
End Sub

Fluff, your macro works perfectly. Thank you very much. It's a luxury to have people like you in this fantastic forum.

 

[Fluff]

You're welcome & thanks for the feedback.