VBA Utiliser un For Next et un With

Version imprimable

Bonjour le Forum!

Je développe actuellement un bout de code pour réaliser une action sur plusieurs feuilles: modifier certaines plages en indiquant la valeur de la cellule à la place de la formule.
D'autres feuilles ne doivent, elles, pas être modifiées (celles-ci sont connues et leur nom restera figé).

Puisque je veux parer l'ajout de nouveaux onglets dont je ne connaitrai pas le nom, j'ai tenté de mettre en place une solution dynamique qui compte les feuilles, puis applique l'action que je souhaite sur ces dernières tout en évitant cette action sur celles qui ne doivent pas être modifiées.

Concrètement:
- Un ensemble de feuilles connues ne doit pas être modifié,
- Un ensemble variable de feuilles de nom inconnu doit être modifié.

Voici mon 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 Essai() Dim myRange As Range, myRange1 As Range, myRange2 As Range, myRange3 As Range Dim derniereLigne As Long Dim i As Integer derniereLigne = Range("E7").End(xlDown).Row For i = 1 To Worksheets.Count If Worksheets(i).Name = "Data" Or Worksheets(i).Name = "Budget_2015" _ Or Worksheets(i).Name = "Budget_2016" Or Worksheets(i).Name = "Budget_2017" _ Or Worksheets(i).Name = "Liste des centres" Or Worksheets(i).Name = "CA_Moyens" Then Else 'Ici je peux voir que le nom de chaque apparait bien par des MsgBox successifs, mais l'action suivante ne fonctionne pas MsgBox Worksheets(i).Name With Worksheets(i).Name Set myRange = Range(Cells(10, 3), Cells(derniereLigne, 10)) Set myRange1 = Range(Cells(10, 14), Cells(derniereLigne, 15)) Set myRange2 = Range(Cells(10, 19), Cells(derniereLigne, 20)) Set myRange3 = Range(Cells(10, 22), Cells(derniereLigne, 25)) myRange.Value = myRange.Value myRange1.Value = myRange1.Value myRange2.Value = myRange2.Value myRange3.Value = myRange3.Value End With End If Next i End Sub
L'action s'effectue bien si je me place sur la feuille en question, mais pas d'action sur les autres en revanche.
J'imagine qu'il doit y avoir un lien avec le With, mais je ne vois pas trop pourquoi ni comment...

Merci d'avance pour votre aide ou vos axes de réflexions ;)!

Bonne journée,

Esculape.

13/05/2016, 11h59
Kiouane

Je ne sais pas si c'est ça mais je crois que tu dois enlever le .Name après With Worksheet(i).
13/05/2016, 12h03
Esculape

Hello Kiouane!

Non ça ne marche pas mieux, désolé.
13/05/2016, 12h06
Kiouane

Si j'ai bien compris, tu veux remplacer les formules dans les cellules avec les valeurs retournées par ces même formules? Si c'est ça il faut passer par un collage spéciale.

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
Sub Essai()
Dim myRange As Range, myRange1 As Range, myRange2 As Range, myRange3 As Range, S As Worksheet
Dim derniereLigne As Long
Dim i As Integer
For Each S In Worksheets
    If S.Name = "Data" Or S.Name = "Budget_2015" _
        Or S.Name = "Budget_2016" Or S.Name = "Budget_2017" _
        Or S.Name = "Liste des centres" Or S.Name = "CA_Moyens" Then
    Else
        'Ici je peux voir que le nom de chaque apparait bien par des MsgBox successifs, mais l'action suivante ne fonctionne pas
        MsgBox S.Name
        With S
             derniereLigne = .Range("E7").End(xlDown).Row
            Set myRange = .Range(.Cells(10, 3), .Cells(derniereLigne, 10))
            Set myRange1 = .Range(.Cells(10, 14), .Cells(derniereLigne, 15))
            Set myRange2 = .Range(.Cells(10, 19), .Cells(derniereLigne, 20))
            Set myRange3 = .Range(.Cells(10, 22), .Cells(derniereLigne, 25))
            myRange.Value = myRange.Value
            myRange1.Value = myRange1.Value
            myRange2.Value = myRange2.Value
            myRange3.Value = myRange3.Value
        End With
    End If
Next
End Sub

13/05/2016, 14h08
Esculape

Kiouane: oui c'est ça!
J'ai déjà essayé le collage spécial mais je ne trouve pas cela optimal.

Une simple transposition Value = Value marche très bien, mais c'était mon With qui posait réellement problème.

rdurupt: Merci, ça marche!
Du coup il faut attribuer directement une variable à l'objet Worksheet si j'ai bien tout saisi?

Ca rejoint ce que tu disait finalement Kiouane.

Merci pour votre aide précieuse ;)!

A bientôt.
13/05/2016, 14h14
MacKay77

Bonjour,

Sauf erreur de ma part, ton erreur venait bien du with qui n'était pas utilisé.
Il fallait rajouter un point devant les range et cells pour les relier au worksheet définit par le with.

Bonne journée ;)
13/05/2016, 14h20
Invité
l'erreur venait de la ligne 22 With Worksheets(i).Name qui pour le coup n'est pas un objet mais un string!

plus Set myRange = Range(Cells(10, 3), Cells(derniereLigne, 10)) qui fait référence à la feuille active et pas au With , oui il fallait également [.]

For Each S In Worksheets c'est plus propre et ça référence la feuille sur un objet!
Code:

1 2 derniereLigne = Range("E7").End(xlDown).Row For i = 1 To Worksheets.Count
Faisait toujours référencer à la feuille active!
je fais référencer à la feuille de la boucle For Each S In Worksheets
Code:

1 2 With S derniereLigne = .Range("E7").End(xlDown).Row
With permet d’énumérer toutes les propriétés et méthode d'un objet sens devoir réécrire toute sa déclinaison!
13/05/2016, 16h58
Esculape

Super!

Merci beaucoup pour ces réponses rdurupt. Petit à petit je commence à mieux cerner les subtilités de VBA!
Merci aussi MacKay77!

C'est toujours un plaisir d'apprendre sur developpez!

A bientôt le forum ;).

Esculape.