VBA find missing numbers and result of missing numbers place under their corresponding header

[motilulla]

Hello,

I need a macro help, which can find the missing numbers missing from 1 to 30 in the various ranges. And I want the result of missing number must show under their corresponding header numbers, as shown below in the Example sheet1...also image is for more detail.

When I run the macro it must find missing numbers "from 1 to 30" within 5 rows and 5 columns range but these ranges are variable till end of the data.

For example...
1st starting ranges 5 rows and 5 columns = C3:G7, Missing number in the Cell I7 to right under their corresponding header numbers
2nd starting ranges 5 rows and 5 columns = C4:G8, Missing number in the Cell I8 to right under their corresponding header numbers
3rd starting ranges 5 rows and 5 columns = C5:G9, Missing number in the Cell I9 to right under their corresponding header numbers
4th starting ranges 5 rows and 5 columns = C6:G10, Missing number in the Cell I10 to right under their corresponding header numbers... and so on till end of the data

Example sheet1

*	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
1
2			Rn1	Rn2	Rn3	Rn4	Rn5		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
3			2​	7​	28​	22​	13​
4			24​	20​	11​	23​	12​
5			11​	18​	28​	1​	29​
6			9​	6​	9​	17​	8​
7			22​	11​	24​	7​	26​				3	4	5					10				14	15	16			19		21				25		27			30
8			20​	21​	9​	12​	20​			2	3	4	5					10			13	14	15	16			19						25		27			30
9			30​	16​	25​	14​	4​			2	3		5					10			13		15				19				23				27
10			4​	17​	19​	14​	6​		1	2	3		5					10			13		15			18					23				27	28	29
11			22​	5​	16​	25​	18​		1	2	3					8		10			13		15								23				27	28	29
12			21​	23​	21​	25​	25​		1	2	3				7	8		10	11		13		15									24		26	27	28	29
13			2​	2​	14​	12​	20​		1		3				7	8	9	10	11		13		15									24		26	27	28	29
14			7​	22​	23​	29​	18​		1		3					8	9	10	11		13		15									24		26	27	28		30
15			17​	13​	4​	22​	21​		1		3			6		8	9	10	11				15				19					24		26	27	28		30
16			4​	9​	17​	22​	5​		1		3			6		8		10	11				15	16			19					24		26	27	28		30
17			2​	9​	13​	26​	18​		1		3			6		8		10	11				15	16			19					24	25		27	28		30
18			9​	17​	7​	3​	6​		1							8		10	11	12		14	15	16			19	20				24	25		27	28		30
19
20
21

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Rick Rothstein]

See if this macro gives you the results you want...

Sub Motilulla()
  Dim R As Long, LastRow As Long, Cell As Range
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Application.ScreenUpdating = False
  Range("I7:AL" & LastRow) = [COLUMN(1:30)]
  For R = 7 To LastRow
    For Each Cell In Range(Cells(R - 4, "C"), Cells(R, "G"))
      Cells(R, "I").Resize(, 30).Replace Cell.Value, "", xlWhole, , , , False, False
    Next
  Next
  Application.ScreenUpdating = True
End Sub

 

[motilulla]

See if this macro gives you the results you want...

Sub Motilulla()
  Dim R As Long, LastRow As Long, Cell As Range
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Application.ScreenUpdating = False
  Range("I7:AL" & LastRow) = [COLUMN(1:30)]
  For R = 7 To LastRow
    For Each Cell In Range(Cells(R - 4, "C"), Cells(R, "G"))
      Cells(R, "I").Resize(, 30).Replace Cell.Value, "", xlWhole, , , , False, False
    Next
  Next
  Application.ScreenUpdating = True
End Sub

Rick Rothstein, it worked well as I wanted just a note: after I run the macro it gets stuck on the line below

Cells(R, "I").Resize(, 30).Replace Cell.Value, "", xlWhole, , , , False, False

after trying and modifying again and again it worked when I finally removed 2 , , in between the xlWhole & False as shown below...May be it is all about due to my version problem

Cells(R, "I").Resize(, 30).Replace Cell.Value, "", xlWhole, , False, False

I appreciate your help and time you take to resolve this complicated query.

Good luck

Kind Regards,
Moti

 

[Sektor]

Workbook
Code:

Sub FindMissingNums()
    Dim rw&, num&, rng As Range, cell As Range, is_found As Boolean
    For rw = 3 To Cells(Rows.Count, 3).End(xlUp).Row
        Set rng = Cells(rw, 3).Resize(5, 5)
        For num = 1 To 30
            is_found = False
            For Each cell In rng
                If cell = num Then
                    is_found = True
                    Exit For
                End If
            Next
            If Not is_found Then
                Cells(rw, num + 8) = num
            End If
        Next
    Next
End Sub

 

[motilulla]

Workbook
Code:

Sub FindMissingNums()
    Dim rw&, num&, rng As Range, cell As Range, is_found As Boolean
    For rw = 3 To Cells(Rows.Count, 3).End(xlUp).Row
        Set rng = Cells(rw, 3).Resize(5, 5)
        For num = 1 To 30
            is_found = False
            For Each cell In rng
                If cell = num Then
                    is_found = True
                    Exit For
                End If
            Next
            If Not is_found Then
                Cells(rw, num + 8) = num
            End If
        Next
    Next
End Sub

Sektor, I like your macro it is written in a simple way so any changes can be done easier

Yes it is populating the results under their corresponding header, as I wanted ?

I appreciate your help and time you take to resolve this was a complicated query.

Good luck you have a nice day

Kind Regards,
Moti

 

[Sektor]

@motilulla You're welcome!

 

[Fluff]

@Sektor In future when somebody has been told to start a new thread, please post your solution to the new thread, not the old one.
I have merged your response to the correct thread.