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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
| Private Sub CommandButton1_Click()
'Ask for the address
Dim Promp As String ' variable for the text on InputBox
Dim Tit As String ' Variable for the title of InputBox
Dim Difault As String ' Variable of Default value of InputBox
Dim newproperty As Variant ' Variable of my data
'Text of the inputbox
Promp = "You would like to add a new property on the " & vbCrLf & _
"board, however we are going to check first if this property does not exists already." & _
vbCrLf & vbCrLf & _
" Please enter the addess on the check box"
'Title of the inputbox
Tit = "Add a new property on the board"
'Text in the checkbox
Difault = "Property Address Example : 32 WILSONS ROAD"
'variable name
newproperty = InputBox(Promp, Tit, Difault)
'Loop in case of error from the user
Do While newproperty = "" Or newproperty = " " Or newproperty = "Property Address Example : 32 WILSONS ROAD"
'variable name
newproperty = InputBox(Promp, Tit, Difault)
Loop
' ici il faudrait ajouter lopportunite pour lutilisateur de quitter la fonction en cliquant sur le bouton cancel de linput box, pour eviter une boucle infinie
'Use the newproperty variable in searh the data on the table
Dim RG As Range
Dim Propertyview As Worksheet
Set RG = Range("c6:C500")
Set Propertyview = Sheets("Property view")
Dim myresearch As Boolean
If newproperty <> "" Or newproperty <> " " Or newproperty <> "Property Address Example : 32 WILSONS ROAD" Then
Active.Propertyview
myresearch = RG.Find(newproperty, RG, xlValue, xlPart, xlbyrow, xlNext)
End If
'If y research = not nothing
If myresearch = False Then
'Text of the msgbox
Promp = " We can not add this property on the table as it already exists." & vbCrLf & _
"Please use Update property button on the General Board."
'Title of the msgbox
Tit = "Sorry, we can not add this property"
'variable name
myresearchisfound = MsgBox(Promp, vbOKOnly, Tit)
End If
' End of the process
If vbOK Then Exit Sub
'If y research = nothing
If myresearch = True Then
'add the line on the table
Sheets("Property view").Select
Rows("6:6").Select
Selection.Insert Shift:=xlDown
Rows("8:8").Select
Selection.Copy
Rows("6:6").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C6").Select
'inform the user the property has been added
Else
'Text of the msgbox
Promp = "The property has been added in the first column of the table."
'Title of the msgbox
Tit = "The property has been added"
'variable name
myresearchisnotfound = MsgBox(Promp, vbOKOnly, Tit)
End If
End Sub |
Recherche donnee dans feuille 2
et pour la partie Si la valeur est trouvee alors ajouter une nouvelle ligne.
et je fais de mon mieux !
Répondre avec citation
je reçois volontiers les critiques qui me feront progresser, mais aussi un 
(ça fait toujours plaisir de savoir que l on a pu dépanner une personne 
Envoyé par Mississipi
pour clore cette discussion.
) Je ne vois pas de boucle dans ton code.
merci milles fois !
pour voir comment fonctionne tout ce que je t'ai présenté
)
Partager