Sub or function not defined - boucle if conditions multiples

Version imprimable

Bonjour,

Dans le fichier ci joint l'utilisateur devra renseigner :
- Type of project
- Where
- Type of service
- Duration of the project
- Workforce

Je veux lui indiquer quels seront les differents HSE requirements lies (voir feuille HSE : 432 scenarios possibles)

Voici la macro :
Code:

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 Sub RA() Dim i As Integer Dim x1, x2, x3, x4, x5 As String ' x1 = Type of project' ' x2 = Where' ' x3 = Type of water' ' x4 = Duration' ' x5 = Workforce' x1 = Sheets("HSE").Range("B4").Value x2 = Sheets("HSE").Range("D4").Value x3 = Sheets("HSE").Range("B6").Value x4 = Sheets("HSE").Range("D6").Value x5 = Sheets("HSE").Range("B10").Value For i = 5 To 436 If Sheets1("B" & i).Value = x1 And Sheets1("C" & i).Value = x2 And Sheets1("D" & i).Value = x3 And Sheets1("E" & i).Value = x4 And Sheets1("F" & i).Value = x5 Then Sheets("HSE").Range("H4").Value = Sheets1("A" & i).Value End If Next i End Sub
Quelque soit le nom de la procedure choisi le message suivant s'affiche : "Sub or function not defined"

Pouvez vous m’éclairer ?

Merci beaucoup !

13/07/2014, 10h07
Theze

Bonjour,

C'est

Code:

Sheets("B" & i)

et non

Code:

Sheets1("B" & i)

Hervé.
13/07/2014, 10h08
SfJ5Rpw8

bonjour,

d'ou viens ton :

Code:

Sheets1(
13/07/2014, 10h11
Philippe Tulliez

Bonjour,
Afin que la lecture de ton code soit aisée, merci de le placer entre les balises prévues à cet effet. (bouton symbolisé pas un croisillon # dans l'éditeur)
Ensuite indiquer le n° de la ligne où l'erreur est signalée serait un plus.
Sans avoir examiné tout ton code, je vois déjà un problème ici

Code:

If Sheets1("B" & i).Value = x1 And Sheets1("C" & i).Value = x2 And Sheets1("D" & i).Value = x3 And Sheets1("E" & i).Value = x4 And Sheets1("F" & i).Value = x5 Then

Sheets1 n'est pas un Range
Attention aussi à cette ligne

Code:

Dim x1, x2, x3, x4, x5 As String

Seul x5 est un String, les autres sont de type Variant
La bonne syntaxe est
Dim x1 as String, x2 as String, etc...
13/07/2014, 10h19
zebeginer

Merci beaucoup pour vos premières remarques.

Code:

If Sheets1("B" & i).Value = x1

Comment puis je faire référence au contenu de la cellule en question qui se trouve sur la feuille 1 ?
13/07/2014, 10h25
SfJ5Rpw8

ben si ta feuille se nomme "Feuille 1" cela donne :

Code:

If ThisWorkbook.Sheets("Feuille 1").range("B" & i).Value = x1

Voici le code rectifie :

Code:

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
Sub RA()
 
Dim i As Integer
Dim x1 As String, x2 As String, x3 As String, x4 As String, x5 As String
 
' x1 = Type of project'
' x2 = Where'
' x3 = Type of water'
' x4 = Duration'
' x5 = Workforce'
 
x1 = Sheets("HSE").Range("B4").Value
x2 = Sheets("HSE").Range("D4").Value
x3 = Sheets("HSE").Range("B6").Value
x4 = Sheets("HSE").Range("D6").Value
x5 = Sheets("HSE").Range("B10").Value
 
For i = 5 To 436
     If ThisWorkbook.Sheets("Sheet 1").Range("B" & i).Value = x1 And ThisWorkbook.Sheets("Sheet 1").Range("C" & i).Value = x2 And ThisWorkbook.Sheets("Sheet 1").Range("D" & i).Value = x3 And ThisWorkbook.Sheets("Sheet 1").Range("E" & i).Value = x4 And ThisWorkbook.Sheets("Sheet 1").Range("F" & i).Value = x5 Then
 
        ThisWorkbook.Sheets("HSE").Range("H4").Value = ThisWorkbook.Sheets("Sheet 1").Range("A" & i).Value
 
 
        Else
 
        End If
 
    Next i
 
 
 
End Sub

J'obtiens désormais le message : "Subscript out of range"

13/07/2014, 10h56
SfJ5Rpw8

lorsque tu obtiens ce message l'affichage doit te montrer la ligne de code en cause .. qu'elle est cette ligne ?
13/07/2014, 11h33
zebeginer

Rien n'est surligné.

Par contre quand je clique sur la commande Debug, le nom de la procédure est surligné

Code:

Sub RA()

Bonjour,
Pour rendre plus lisible ton code et pour te faciliter le travail de maintenance et de débogage, je te conseille l'utilisation de variable objet ainsi que le bloc With..End With
Exemple :

Code:

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
 Dim i As Integer
 Dim x1 As String, x2 As String, x3 As String, x4 As String, x5 As String
 Dim shtSource As Worksheet, shtTarget As Worksheet
 With ThisWorkbook
  Set shtSource = .Worksheets("Sheet 1"): Set shtTarget = .Worksheets("HSE")
 End With
 ' x1 = Type of project'
 ' x2 = Where'
 ' x3 = Type of water'
 ' x4 = Duration'
 ' x5 = Workforce'
 With shtTarget
  x1 = .Range("B4").Value
  x2 = .Range("D4").Value
  x3 = .Range("B6").Value
  x4 = .Range("D6").Value
  x5 = .Range("B10").Value
 End With
 With shtSource
  For i = 5 To 436
   If .Range("B" & i).Value = x1 _
      And .Range("C" & i).Value = x2 _
      And .Range("D" & i).Value = x3 _
      And .Range("E" & i).Value = x4 _
      And .Range("F" & i).Value = x5 Then
      shtTarget.Range("H4").Value = .Range("A" & i).Value
     Else
   End If
  Next i
 End With

En lieu et place de variable objet de type Worksheet, tu peux aussi utiliser la propriété CodeName de la feuille plutôt que sa propriété Name

13/07/2014, 12h57
zebeginer

1 pièce(s) jointe(s)

Merci pour cette suggestion. Malheureusement l'erreur "Subscript out of range est toujours presente.

Code:

Set shtSource = .Worksheets("Sheet 1")

est surligne en jaunePièce jointe 151073
13/07/2014, 13h27
Philippe Tulliez

Bonjour,

Citation:

Merci pour cette suggestion. Malheureusement l'erreur "Subscript out of range est toujours presente.

Je n'ai pas dit que le problème venait de là, j'ai simplement suggéré d'utiliser les variables objets afin de facilité la maintenance ainsi que la lecture du code.
La feuille Sheet 1 fait elle partie du même classeur que là où se trouve le code VBA ? si pas, il faut bien entendu préciser le classeur.
13/07/2014, 14h05
mercatog

Le nom de ta feuille est Sheet1 et non Sheet 1

Allons, un peux d'application

Merci beaucoup, cela a fonctionne un moment.

Mais un autre message apparait desormais "Compile error, syntax error" alors que je n'ai rien modifie...
Le nom de la procedure est surligne en jaune suite a l'apparition de ce message.

Voici le code:

Code:

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
Sub Ra()
 
Dim i As Integer,
 Dim x1 As String, x2 As String, x3 As String, x4 As String, x5 As String
 Dim shtSource As Worksheet, shtTarget As Worksheet
 With ThisWorkbook
  Set shtSource = .Worksheets("Matrix"): Set shtTarget = .Worksheets("HSE")
 End With
 ' x1 = Type of project'
 ' x2 = Where'
 ' x3 = Type of water'
 ' x4 = Duration'
 ' x5 = Workforce'
 With shtTarget
  x1 = .Range("A4").Value
  x2 = .Range("A8").Value
  x3 = .Range("A6").Value
  x4 = .Range("A12").Value
  x5 = .Range("A10").Value
 End With
 With shtSource
 i = 5
  For i = 5 To 436
   If .Range("B" & i).Value = x1 _
      And .Range("C" & i).Value = x3 _
      And .Range("D" & i).Value = x2 _
      And .Range("E" & i).Value = x4 _
      And .Range("F" & i).Value = x5 Then
 
       shtTarget.Range("G4").Value = .Range("A" & i).Value
 
     Else
   End If
  Next i
 End With
 
 
 End Sub

15/07/2014, 14h39
SfJ5Rpw8

bonjour,

est-tu de ne voir rien d'autre que le nom de ta procédure en jaune ? ... rien n'est sélectionné dans ton code...?

pourquoi ta ligne Dim i... se termine-t-elle par une virgule ?