Excel VBA - Erreur 1004

Bonjour tout le monde,

Je suis en train de développer une macro permettant de lister les recettes qui utilisent chaque ingrédient.

Je m'explique :

J'ai un fichier "Validation_substances" contenant la liste de mes ingrédients.

J'ai une foule d'autres fichiers avec des recettes (utilisant mes ingrédients)

=> Je souhaite savoir quelle(s) recette(s) est (sont) associée(s) à chacun de mes ingrédients.

J'ai donc ce code :

Sub save()

Dim cell_f As Range
Dim cell_l As Range
Dim cell_LF As Range
Dim codef As String
Dim nom As String
Dim DerLig_f As Integer
Dim DerLig_l As Integer
Dim ws_Fcheck As Worksheet

nom = ActiveWorkbook.Name 'nom = fichier excel regulatory_check renommé
codef = Workbooks(nom).Worksheets("formula_check").Range("A4").Value & ";" 'codef = "code formule;"
chemin = Worksheets("macro").Range("B1").Value
vsubst_file = Worksheets("macro").Range("B2").Value 'vsubst_file = fichier validation de substance
vsubst_ws = Worksheets("macro").Range("B3").Value 'vsubst_ws = onglet List du fichier validation susbtances
Set ws_Fcheck = Worksheets("formula_check") 'ws_Fcheck = onglet formula_check du fichier regulatory_check

On Error GoTo ErrorHandler

Set wb_list = Workbooks.Open(chemin & "\" & vsubst_file)
DerLig_l = wb_list.Worksheets(vsubst_ws).Cells(Rows.Count, 1).End(xlUp).Row
DerLig_f = Workbooks(nom).Worksheets("formula_check").Cells(Rows.Count, 1).End(xlUp).Row

Set cell_LF = Worksheets(vsubst_ws).Range(Cells(4, 5), Cells(DerLig_l, 5)).Find(codef)
    If Not cell_LF Is Nothing Then
        Do
            cell_LF = Replace(cell_LF.Value, codef, "")
            Set cell_LF = Worksheets("List").Range("E4:E5000").FindNext(cell_LF)
        Loop While Not cell_LF Is Nothing
    End If

For Each cell_f In ws_Fcheck.Range(Cells(8, 1), Cells(DerLig_f, 1)) 'traverse les substances de la formule
    For Each cell_l In wb_list.Worksheets("List of components").Range("AR4:AR5000") 'traverse les substances
        If cell_f.Value = cell_l.Value Then 'si la substance est utilisée dans la formule
            wb_list.Worksheets("List of components").Cells(cell_l.Row, 5) = wb_list.Worksheets("List of components") _
            .Cells(cell_l.Row, 5).Text & codef 'le nom de la formule apparait sur la ligne de la substance
        End If
    Next cell_l
Next cell_f

'******************************************************* GESTION DES ERREURS *******************************************************'

ErrorHandler:
If Err.Number = 1004 Then
    MsgBox "You have to save and close the Workbook Feel_Good_Regulatory_validation"
End If

End Sub

L'erreur se trouve à ce niveau : je pense que c'est le Range qui ne fonctionne pas, j'ai une erreur 1004 à chaque fois mais je suis incapable de trouver pourquoi...

Set cell_LF = Worksheets(vsubst_ws).Range(Cells(4, 44), Cells(DerLig_l, 44)).Find(codef)
    If Not cell_LF Is Nothing Then
        Do
            cell_LF = Replace(cell_LF.Value, codef, "")
            Set cell_LF = Worksheets("List").Range("AR4:AR5000").FindNext(cell_LF)
        Loop While Not cell_LF Is Nothing
    End If

Même problème à ce niveau :

For Each cell_f In ws_Fcheck.Range(Cells(8, 1), Cells(DerLig_f, 1)) 'traverse les substances de la formule
    For Each cell_l In wb_list.Worksheets("List").Range("AR4:AR5000") 'traverse les substances
        If cell_f.Value = cell_l.Value Then 'si la substance est utilisée dans la formule
            wb_list.Worksheets("List").Cells(cell_l.Row, 3) = wb_list.Worksheets("List") _
            .Cells(cell_l.Row, 44).Text & code 'le nom de la formule apparait sur la ligne de la substance
        End If
    Next cell_l
Next cell_f

Aussi, tant que j'y suis, est ce qu'il y a une meilleure solution pour la gestion des erreurs ?

En effet, si mon fichier "validation_substances" est ouvert j'ai systématiquement une erreur. J'ai donc rajouté le ErrorHandler mais je ne sais pas si c'est ce qu'il y a de plus adapté à mon problème...

Merci à vous !

Bonjour,

Set cell_LF = Worksheets(vsubst_ws).Range(Cells(4, 44), Cells(DerLig_l, 44)).Find(codef)

Lors de l'erreur contyrôler :

• est-ce que vsubst_ws a bien le bon nom d'onglet ?
• est-ce que DerLig_l a bien la bonne valeur ?

With Worksheets(vsubst_ws)
Set cell_LF = .Range(.Cells(4, 44), .Cells(DerLig_l, 44)).Find(codef)
End With

regarde les . ajoutés devant Cells

eric

Bonjour Eric,

Merci pour ta réponse.

Pour faciliter la macro, j'ai remplacé la variable vsubst_ws par une variable Worksheets correspondant à l'onglet.

La solution en utilisant With fonctionne très bien. J'avoue que je ne comprend pas ce qu'il se passe par contre... Quelle est la différence avec l'écriture initiale ?

Sinon je voulais avoir votre avis sur ma gestion des erreurs ? (qui est seulement liée à l'ouverture d'un fichier) est e qu'il y a une meilleure solution selon vous ?

Nuns

1) dans

Set cell_LF = Worksheets(vsubst_ws).Range(Cells(4, 44), Cells(DerLig_l, 44)).Find(codef)

Cells(4, 44) et Cells(DerLig_l, 44) font référence à la feuille active qui n'est peut-être pas celle que tu souhaites.

avec

With Worksheets(vsubst_ws)

.Range(), .Cells(4, 44) et .Cells(DerLig_l, 44) font référence à Worksheets(vsubst_ws)

2) je n'ai rien contre les traitements d'erreur.

Par contre une autre façon de faire est de les éviter en traitant en amont : chemin correct et nom de fichier sans caractères interdits au moment de la saisie.

eric