VBA find missing numbers in the various ranges

[motilulla]

Hello,

I need a macro help, which can find the missing numbers missing from 1 to 30 in the various ranges.

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
2nd starting ranges 5 rows and 5 columns = C4:G8, Missing number in the Cell I8 to right
3rd starting ranges 5 rows and 5 columns = C5:G9, Missing number in the Cell I9 to right
4th starting ranges 5 rows and 5 columns = C6:G10, Missing number in the Cell I10 to right... and so on till end of the data

Example sheet1 also image attached

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

2

Rn1

Rn2

Rn3

Rn4

Rn5

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

26

28

8

20​

21​

9​

12​

20​

2

3

5

10

13

15

19

27

9

30​

16​

25​

14​

4​

2

3

5

10

13

15

23

27

10

4​

17​

19​

14​

6​

1

2

3

10

13

15

23

27

28

29

11

22​

5​

16​

25​

18​

12

21​

23​

21​

25​

25​

13

2​

2​

14​

12​

20​

14

7​

22​

23​

29​

18​

15

17​

13​

4​

22​

21​

16

4​

9​

17​

22​

5​

17

2​

9​

13​

26​

18​

18

9​

17​

7​

3​

6​

19

20

21

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

How about

Sub motilulla2()
   Dim i As Long, j As Long, k As Long
   Dim Fnd As Range
   
   k = 8
   For i = 3 To Range("C" & Rows.Count).End(xlUp).Row - 4
      For j = 1 To 30
         Set Fnd = Range("C" & i).Resize(5, 5).Find(j, , , xlWhole, , , , , False)
         If Fnd Is Nothing Then
            k = k + 1
            Cells(i + 4, k) = j
         End If
      Next j
      k = 8
   Next i
End Sub

 

[Sektor]

Workbook
Code:

Sub FindMissingNums()
    Dim rw&, num&, col&, rng As Range, cell As Range, is_found As Boolean
    For rw = 3 To Cells(Rows.Count, 3).End(xlUp).Row
        col = 8
        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
                col = col + 1
                Cells(rw, col) = num
            End If
        Next
    Next
End Sub

 

[motilulla]

How about

Sub motilulla2()
   Dim i As Long, j As Long, k As Long
   Dim Fnd As Range
  
   k = 8
   For i = 3 To Range("C" & Rows.Count).End(xlUp).Row - 4
      For j = 1 To 30
         Set Fnd = Range("C" & i).Resize(5, 5).Find(j, , , xlWhole, , , , , False)
         If Fnd Is Nothing Then
            k = k + 1
            Cells(i + 4, k) = j
         End If
      Next j
      k = 8
   Next i
End Sub

Fluff, after running your macro, this time also I found my mistakes in an example attached. Your macro worked flawless and answered the correct missing numbers in each range as appeal.

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

Good luck

Kind Regards,
Moti

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Marc L]

Pretty the same way :​

Sub Demo1()
        Dim V, R&, C%, N%
    With [C2].CurrentRegion.Rows
      ReDim V(1 To .Count - 5, 1 To 30)
        For R = 2 To .Count - 4
            C = 0
        For N = 1 To UBound(V, 2)
            If .Item(R).Resize(5).Find(N, , , 1) Is Nothing Then C = C + 1: V(R - 1, C) = N
        Next N, R
           .Cells(6, .Columns.Count + 2).Resize(R - 2, N - 1).Value2 = V
    End With
End Sub

 

[motilulla]

Workbook
Code:

Sub FindMissingNums()
    Dim rw&, num&, col&, rng As Range, cell As Range, is_found As Boolean
    For rw = 3 To Cells(Rows.Count, 3).End(xlUp).Row
        col = 8
        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
                col = col + 1
                Cells(rw, col) = num
            End If
        Next
    Next
End Sub

Sektor, thank you for giving another option your macro track even last row missing numbers.

I appreciate your help and time you take to give an additional option.

Good luck

Kind Regards,
Moti

 

[motilulla]

Pretty the same way :​

Sub Demo1()
        Dim V, R&, C%, N%
    With [C2].CurrentRegion.Rows
      ReDim V(1 To .Count - 5, 1 To 30)
        For R = 2 To .Count - 4
            C = 0
        For N = 1 To UBound(V, 2)
            If .Item(R).Resize(5).Find(N, , , 1) Is Nothing Then C = C + 1: V(R - 1, C) = N
        Next N, R
           .Cells(6, .Columns.Count + 2).Resize(R - 2, N - 1).Value2 = V
    End With
End Sub

Marc L, thank you macro worked as treat.

I am pleased about your help and time you take to give an extra option.

Good luck

Kind Regards,
Moti

 

[Sektor]

You're welcome! ?

 

[motilulla]

Hello Fluff,

I need a bit change in the result layout post#2 macro is giving the result, as I wanted.

Please can you take a look if it is possible I get the result of missing number under their corresponding header number as shown below?

Example sheet1 and the image are attached also.

*	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