Macro lente : remplacer vide par formule

A

Acturis

XLDnaute Nouveau
Bonjour à tous,

Je me permet de venir vers vous pour solliciter votre aide. Dans mon fichier excel j'ai besoin, via une macro, de remplacer toutes les cellules vides d'une plage par une formule RechercheV. J'ai construit une macro qui fonctionne (en cherchant sur internet), elle fonctionne, cependant je la trouve très lente car elle tourne environ 2 minutes pour une petite plage de 500 lignes au sein d'une colonne bien spécifique.

Voici le code :

VB: 
Sub VPcleaning()
'
'
Sheets("Detailed Assessment").Select
     Dim I As Long
  With Sheets("Detailed Assessment")
  .Activate
    For I = 6 To 505
     If .Cells(I, 19) = "" Then
       .Cells(I, 19).FormulaR1C1 = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
     End If
    Next I
  End With

End Sub

Je ne m'y connais pas vraiment en macro. Voyez vous un moyen d’accélérer le processus ?

Le but étant de faire en sorte que si les cellules de la plage (S6;S505), S étant ma colonne 19, et bien le contenu soit automatiquement remplacé par la formule citée dans le code.
Avec mon code je pense que la macro check les cellules une par une, ce qui prend du temps.

Si vous avez un raccourci en tête je suis preneur.

Merci d'avance =)
 
Trier par date Trier par votes
Lone-wolf

Lone-wolf

XLDnaute Barbatruc
Bonsoir Acturis :), le Forum :)

Ici si possible met la formule
.Cells(5, 19).FormulaR1C1 = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"

Ensuite
.Cells(5, 19).Copy .Cells(6, 19)
.Range("P6").AutoFill .Range("P6:505"), Type:=xlFillDefault
 
Vote positif 0 Vote négatif
A

Acturis

XLDnaute Nouveau
Lone-wolf à dit:
Bonsoir Acturis :), le Forum :)

Ici si possible met la formule
.Cells(5, 19).FormulaR1C1 = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"

Ensuite
.Cells(5, 19).Copy .Cells(6, 19)
.Range("P6").AutoFill .Range("P6:505"), Type:=xlFillDefault
Cliquez pour agrandir...

Bonjour Lone-Wolf,
Merci pour ta réponse, en effet la macro est quasi instantanée :). Cependant on a perdu le check sur les cellules vides. En effet je souhaiterais que cette formule ne s'applique que dans les cellules vides de la plage. Tu aurais une idée ?

Voici le code modifié avec ton approche.

VB: 
Sub VPcleaning()
'
'
Sheets("Detailed Assessment").Select
Dim I As Long
  With Sheets("Detailed Assessment")
  .Activate
.Cells(506, 19).FormulaR1C1 = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
.Cells(506, 19).Copy .Cells(6, 19)
.Range("S6").AutoFill .Range("S6:S505"), Type:=xlFillDefault

End With
    
End Sub

Merci
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonsoir Acturis, Lone-wolf,
Code: 
Sub VPcleaning()
On Error Resume Next 'si aucune SpecialCell
Sheets("Detailed Assessment").[S6:S505].SpecialCells(xlCellTypeBlanks) _
    = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
End Sub
A+
 
Vote positif 0 Vote négatif
A

Acturis

XLDnaute Nouveau
J'ai suivi ton conseil, cela fonctionne mais la macro est vraiment super lente....avec 450 cellules de la plage vides, la macro a tournée 3 minutes.
N'y a t'il pas un moyen de faire comme dans ta première approche, mais en définissant une plage comme cela Range(Premiere cellule de la plage(S6;S505)vide:s505) ? En effet les cellules non vides seront toujours placées au début de la plage, et toutes les autres seront vides...par contre des vides il peut y en avoir 450, 200 ou 30 par exemple.

Dernier code avec tes conseils

VB: 
Sub VPcleaning()
'
'
Sheets("Detailed Assessment").Select
Dim I As Long
  With Sheets("Detailed Assessment")
  .Activate
Dim plage As Range, cel As Range
Set plage = .Range("S6:S505")
.Cells(506, 19).FormulaR1C1 = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"

For Each cel In plage
If cel = vbNullString Then .Cells(506, 19).Copy cel
Next cel
End With
    
End Sub

Merci
 
Vote positif 0 Vote négatif
A

Acturis

XLDnaute Nouveau
job75 à dit:
Bonsoir Acturis, Lone-wolf,
Code: 
Sub VPcleaning()
On Error Resume Next 'si aucune SpecialCell
Sheets("Detailed Assessment").[S6:S505].SpecialCells(xlCellTypeBlanks) _
    = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
End Sub
A+
Cliquez pour agrandir...

Merci Job75 pour ta réponse.
Avec ce code la macro fonctionne superbement bien. Le seul petit hic, c'est que j'ai l'impression qu'il ne reconnait pas les cellules vides. Par défaut rien ne se passe, par contre si je sélectionne des cellules vides dans mon onglet et que je clic sur "Suppr", alors là la macro va fonctionner correctement et quasi instantanément. Tu as une idée qui permettrait de corriger cela ?

Merci d'avance
 
Vote positif 0 Vote négatif
A

Acturis

XLDnaute Nouveau
Après avoir cherché un peu il semblerait que mes cellules ne soient pas vraiment vide mais contiennent du texte ""...même si rien ne s'affiche dans la barre de formule lorsque je sélectionne une cellule apparemment vide.
Cela vient probablement du fait que j'ai une formule de ce type =SI(S2="";"";'Detailed Assessment'!S6)

Existe t-il un moyen de dire dans une formule si, que la cellule doit être vraiment vide au lieu de "" ? ^^
Ou bien contourner le problème dans le code de Job75 ?

Merci
 
Vote positif 0 Vote négatif
A

Acturis

XLDnaute Nouveau
J'ai essayé ta dernière proposition Lone-wolf et pareil, ça prend beaucoup de temps :/

VB: 
Sub VPcleaning()
'
'

Sheets("Detailed Assessment").Select
Dim I As Long
  With Sheets("Detailed Assessment")
  .Activate
  For I = 6 To 505
If .Cells(I, "S") = vbNullString Then .Cells(I, "S") = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
Next I

End With
End Sub

L'approche de Job75 est intéressante car c'est vraiment rapide...je suis juste bloqué car les cellules ne sont pas vraiment vide :/ Il n'y aurais pas une astuce à glisser dans son code pour contourner le pb des cellules "" quasi vides ? ^^

VB: 
Sub VPcleaning()
On Error Resume Next 'si aucune SpecialCell
Sheets("Detailed Assessment").[S6:S505].SpecialCells(xlCellTypeBlanks) _
    = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
End Sub

Merci en tout cas pour votre aide =)
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re,
Acturis à dit:
Le seul petit hic, c'est que j'ai l'impression qu'il ne reconnait pas les cellules vides.
Cliquez pour agrandir...
Les cellules ne sont donc pas vides : elles contiennent le texte vide "".

Alors voici 2 solutions, testez-les et dites-nous quelle est la plus rapide chez vous :
Code: 
Sub VPcleaning1()
Dim c As Range
With Sheets("Detailed Assessment").[S6:S505]
    For Each c In .Cells
        If CStr(c) = "" Then c = Empty 'efface les textes vides ""
    Next
    On Error Resume Next 'si aucune SpecialCell
    .SpecialCells(xlCellTypeBlanks) = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
End With
End Sub

Sub VPcleaning2()
Dim c As Range, plage As Range
With Sheets("Detailed Assessment").[S6:S505]
    For Each c In .Cells
        If CStr(c) = "" Then Set plage = Union(IIf(plage Is Nothing, c, plage), c)
    Next
    If Not plage Is Nothing Then plage = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
End With
End Sub
Bonne nuit.
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonjour Acturis, Lone-wolf, le forum,

La 2ème solution peut prendre beaucoup de temps si la plage est bien plus grande avec plusieurs centaines de plages "vides" disjointes.

Voici une 3ème solution, la meilleure car elle fonctionne dans tous les cas de figure :
Code: 
Sub VPcleaning3()
Dim tv, tf, f$, i&
With Sheets("Detailed Assessment")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .[S6:S505] 'à adapter, au moins 2 cellules
        tv = .Value: tf = .FormulaR1C1 'tableaux VBA, plus rapides
        f = "=IF(RC[-17]="""","""",VLOOKUP(RC[-17],Data!C[-18]:C[9],28,FALSE))"
        For i = 1 To UBound(tv)
            If CStr(tv(i, 1)) = "" Then tf(i, 1) = f
        Next
        .FormulaR1C1 = tf 'restitution
    End With
End With
End Sub
Elle est très rapide car elle utilise des tableaux VBA.

Bonne journée.
 
Vote positif 0 Vote négatif

Discussions similaires

L
  • Résolu(e)
Microsoft 365 Suppression de cellules vide
Réponses
3
Affichages
187
Le Rouky
L
G
  • Question
Microsoft 365 Saut de ligne dans une cellule
Réponses
3
Affichages
154
GClaire
G
_l_ours
  • Résolu(e)
XL 2016 Macro Dim c As Range, i As Long
Réponses
8
Affichages
227
mapomme
mapomme
G
  • Question
Microsoft 365 Réduire la taille de la macro ?
Réponses
9
Affichages
605
Staple1600
Staple1600
S
  • Question
Microsoft 365 créer un macro vba
Réponses
0
Affichages
154
souhaib ad
S
Dadi147
  • Résolu(e)
XL 2016 cellules A, b, C et D sont automatiquement vides
Réponses
3
Affichages
375
job75
job75
B
  • Résolu(e)
Microsoft 365 affecter des valeurs dans un tableau de noms de variable
Réponses
4
Affichages
402
bérylion
B
3xceln4ute
  • Question
Microsoft 365 VBA export Excel vers Word : Bnq congé 0
Réponses
5
Affichages
319
Phil69970
Phil69970
M
  • Question
Microsoft 365 Macro VBA supprimer une colonne
Réponses
5
Affichages
110
marie3107
M
J
  • Résolu(e)
XL 2013 Macro et script vba : macro s'arrête dès la première condition trouvée
Réponses
11
Affichages
433
Janu44
J

Membres actuellement en ligne

4 membres et 66 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 248
Messages
2 086 593
Membres
103 248
dernier inscrit
Happycat