Isolate a number in string specified to the same number of digit without an extra number attached to it

[Martin1978]

Hi,

i am a building inspector and i am creating a report with equipment which was inspected on Sheet1. On Sheet1 , i am using th column AA to write down notes numbers, per example 1,2,17,18. and each time i am reading note number 1 at AA, i am write down on my Sheet4 at Note 1 column R the page number which we find at column AE from Sheet1 and i keep on compiling the information each time the note 1 is found on Sheet1 column AA of the next row. But my problem is, i don't want to find the note 18 when i am search for note 1 only and if i put filter with a followed number, i am excluding the note 1. My note are currently from 1 to 999, so number like 1 could be repeated often in a number.

Here i attached the rule in part of the excel sheet at the bottom :

thanks in advance for your help!

Sub Pagination_Notes()
Dim NoteRow As Byte, RapportRow, TextePrecedent As String
NoteRow = 12
LastNoteRow = Sheet4.Range("B9999").End(xlUp).Row + 1
RapportRow = 12
LastRapportRow = Sheet1.Range("B9999").End(xlUp).Row - 1
TextePrecedent = ""
Sheet4.Range("R" & NoteRow & ":S" & LastNoteRow).Value = ""
Do While NoteRow < LastNoteRow
If InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value) Then
If InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "1") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "2") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "3") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "4") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "5") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "6") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "7") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "8") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "9") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, Sheet4.Range("C" & NoteRow).Value & "0") Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "1" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "2" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "3" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "4" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "5" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "6" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "7" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "8" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "9" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
ElseIf InStr(Sheet1.Range("AA" & RapportRow).Value, "0" & Sheet4.Range("C" & NoteRow).Value) Then
Bonne_Note = False
Else
Bonne_Note = True
End If
If Bonne_Note = True Then
If TextePrecedent <> Empty Then
Sheet4.Range("R" & NoteRow).Value = TextePrecedent & ", " & Sheet1.Range("AE" & RapportRow).Value
Else
Sheet4.Range("R" & NoteRow).Value = TextePrecedent & Sheet1.Range("AE" & RapportRow).Value
End If
TextePrecedent = Sheet4.Range("R" & NoteRow).Value
End If
RapportRow = RapportRow + 1
Else
RapportRow = RapportRow + 1
If RapportRow = LastRapportRow Then
NoteRow = NoteRow + 1
RapportRow = 12
TextePrecedent = ""
End If
End If
Loop
End Sub

EXCEL SHEET1

HALL D'ENTRÉE

FAP

FC2202

B

A

X

A

A

1,2,17,18

DANS PANNEAU

BAT

2X 12V4A (2019)

B

A

A

A

19

EXCEL SHEET4

MP

1

DDOC4

Aucun exemplaire du rapport d'inspection et de mise à l'essais de l'année précédente ne se trouve sur place. (Selon ULC-S536-04)

Déficience

R COLUMN WHERE THE PAGE IS SHOWING

MP

16

ODISJ1

Ce disjoncteur n'est pas verrouilllé, ni identifié rouge.

Observation

R COLUMN