XL 2016 Macro pour automatiser le nettoyage d'un fichier

[kevint13]

Bonjour tout le monde,
Je sollicite votre aide pour établir une macro qui va changer ma vie !

Voici en détail mon besoin :

Pour chaque colonne du tableau :
- Vérifier que chaque cellule de la colonne ne contient pas de texte. S'il y a du texte (peu importe le texte) remplacer la valeur par la moyenne de la colonne en question :

Une fois la première colonne vérifiée, passer à la colonne suivante et répéter l'opération pour vérifier toutes les colonnes une par une.

Spécificités :
1) J'ai toujours une colonne avec des dates (généralement c'est la première), cette colonne ne doit pas être traitée ou ne doit pas changer.
2) Il m'arrive d'avoir plusieurs niveaux (ligne) de titre, il faudrait peut-être pouvoir indiquer à la macro à quel N° de ligne commencer ? :

J'espère avoir été assez explicite sur la problématique, merci encore pour votre aide !

 

[Staple1600]

Bonsoir le fil

J'espère avoir été assez explicite sur la problématique, merci encore pour votre aide !

Le grand nombre de réponses dans ton fil, prouve que tu as effectivement été explicite 😉

Blague en part, peut-être qu'en joignant un fichier exemple (allégé et anonymisé), on y verrait plus clair et ce serait plus facile pour nous de t'aider, non ?

 

[kevint13]

Bonsoir le fil


Le grand nombre de réponses dans ton fil, prouve que tu as effectivement été explicite 😉

Blague en part, peut-être qu'en joignant un fichier exemple (allégé et anonymisé), on y verrait plus clair et ce serait plus facile pour nous de t'aider, non ?

Merci pour ton retour, en effet avec un exemple c'est plus parlant !
Dans le fichier joint il y a des textes (le texte peut varier du coup il faudrait idéalement qu'il remplace le texte peut importe ce qui est écrit dans la cellule) qui faudrait remplacer par la moyenne de la colonne en question sans toucher au texte en rouge (c'est les titres) ni à la première colonne (c'est la date)

Merci encore pour ton aide !

 

[Staple1600]

Re

Pas sur d'avoir tout compris
Je te laisse tester

Sub test()
Dim LG&
LG = Cells(Rows.Count, 1).End(3).Row
On Error Resume Next
Rows("5:" & LG).SpecialCells(xlCellTypeConstants, 2).Value = 0
Cells(LG + 1, 2).Resize(, 42).FormulaR1C1 = "=AVERAGE(R[-95]C:R[-1]C)"
End Sub

 

[kevint13]

Re

Pas sur d'avoir tout compris
Je te laisse tester

Sub test()
Dim LG&
LG = Cells(Rows.Count, 1).End(3).Row
On Error Resume Next
Rows("5:" & LG).SpecialCells(xlCellTypeConstants, 2).Value = 0
Cells(LG + 1, 2).Resize(, 42).FormulaR1C1 = "=AVERAGE(R[-95]C:R[-1]C)"
End Sub

C'est un excellent début !

Ce qui a bien marché :
- Ne pas impacter la première colonne et les tires
- Remplacer toutes les valeurs textes

Ce qui n'a pas marché :
- remplacer les textes par la moyenne de la colonne en question (actuellement remplacé par 0)

Est ce qu'il faudrait pas faire le remplacement des valeurs colonne par colonne en partant de la 2ème jusqu'à la fin ?
Merci pour ton aide !

 

[Staple1600]

Bonjour le fil

kevint13
Si j'étais moi, je me convaincrais de te suggérer de mettre dans un fichier exemple, deux onglets
AVANT et APRES
On a déjà l'onglet AVANT
Et sur l'onglet APRES, on pourrait voir en ce dimanche matin pluvieux, le résultat (sur une colonne ou deux par exemple) que tu aurais réalisé manuellement.

Ce qui permettrai d'y voir plus clair 😉

 

[kevint13]

Salut Staple1600,
Merci pour ton retour, voici le fichier avant et après
J'ai ajouté sur l'onglet après les actions qui sont réalisées manuellement

 

[Staple1600]

Re

Et cette fois-ci, aprés un excellent début, est-ce une excellente fin ? 😉

Sub test_II()
Dim LG&, CL&, i&, PlG As Range
LG = Cells(Rows.Count, 1).End(3).Row
CL = Cells(5, 2).End(xlToRight).Column
On Error Resume Next
For i = 2 To CL
Set PlG = Cells(5, i).Resize(LG - 4)
PlG.SpecialCells(2, 2).Value = Application.Average(PlG)
Set PlG = Nothing
Next
End Sub

 

[kevint13]

C'EST PARFAIT !!! Tu es au top !

 

[kevint13]

Tu penses qu'il est possible d'ajouter un message box pour inscrire manuellement le nombre de ligne de titre (ça peut varier entre 1 et 4) ?

 

[Staple1600]

Re

Je penses qu'il est d'abord l'heure de l'apéro
Ensuite, je suis pour inciter le demandeur à mettre les mains dans le cambouis pour solutionner sa question.
Normalement avec les bouts de code que j'ai posté dans le fil (et avec l'aide de la touche F1 dans VBE, et du net)
tu devrais être en mesure de faire ce MsgBox 😉
Yercʼh mad !
(ou "à la tienne" pour les gens de France 😉)

 

[kevint13]

Super, merci encore ! et santé !

 

[Staple1600]

Re

Alors tu as trouvé comme afficher ce MsgBox ?

 

[kevint13]

Oui mais j'ai planté la macro .... je pense avoir fait une connerie ... :

Sub nettoyer_fichier()
Dim LG&, CL&, i&, PlG As Range
Dim Titre As Double
Titre = InputBox("Combien de ligne de titre : ", "Ligne de titre")
LG = Cells(Rows.Count, 1).End(Titre - 1).Row
CL = Cells(Titre + 1, 2).End(xlToRight).Column
On Error Resume Next
For i = 2 To CL
Set PlG = Cells(Titre + 1, i).Resize(LG - Titre)
PlG.SpecialCells(2, 2).Value = Application.Average(PlG)
Set PlG = Nothing
Next
End Sub

 

[Staple1600]

Re

Alors, pour te récompenser d'avoir mouillé le maillot et puisque mon verre est vide (et que j'ai plus de cahuètes)
😉

Sub liminal()
Dim i&, Ligne&
For i = 1 To 10
If IsDate(Cells(i, 1)) Then
Ligne = Cells(i, 1).Row
Exit For
End If
Next i
msgbox "Nombre de ligne de titre: " & Ligne - 1, vbInformation, "Fini l'apéro"
End Sub