Macro pour check données et format + génération TXT (tab separator)

**jakomu** · 07/12/2011, 11h48

Bonjour,

Je me rends compte que je n'ai pas toutes les compétences requises à ce que je veux mettre en place, aprés trois jours de galère, je fais appel à vous.

Mon objectif de macro est le suivant :

Dans mon fichier excel (un template), l'utilisateur copie un certain nombre de données.
Dans les autres onglets se trouvent des tables de référence.

1- La macro défini la dernière ligne du tableau présent dans Feuil1

2 - La macro néttoie les valeurs : suppression des " " et "." avant et aprés les valeurs saisies, remplacement des " " par des " " dans les valeurs.

3- La macro force le format de toutes les cellules en Texte.

4 - la macro vérifie que les champs des colonnes à en-têtes verts sont bien remplis (pas de " " ou " " ou "." sinon, cellule colorée rouge), que les valeurs saisies correspondent aux valeurs présentes dans la table de référence associée (dans un autre onglet) sinon, cellule colorée rouge.

5- La macro vérifie que les valeurs présentes dans la colonne bzu sont correctes par rapport à la valeur de la colonne1. Table de référence val/bzu dans l'onglet bzu. Si les valeur ne correspondent pas, cellule de la colonne bzu colorée en rouge.

6 - La macro vérifie que les valeurs de la colonne date (si elles sont présentes, pas obligatoires) sont bien au format DD/MM/YYYY (sinon, cellule colorée en rouge).

7 - Dans la colonne 6, le format doit être le suivant : 145.45
Le point est le séparateur, avec deux décimales aprés le point.
Si la valeur saisie ne se termine pas par ".00", colorer la cellule en gris.

8 - Les valeurs des cellules de la colonne5 ne doivent pas faire plus de 25 caractères, sinon cellule colorée grise.

9 - Si par chance, aucune cellule n'avait été colorée, supprimer toutes les lignes qui se trouvent en dessous du tableau (par sécurité) et générer un fichier txt (tab delimited text) dans un dossier donné.

J'ai pas mal d'idées, j'ai essayer de tourner mon algo dans tous les sens mais je suis définitivement mauvais...ou pas logique.

Merci de votre aide !

Jacques

**ZebreLoup** · 07/12/2011, 11h57

Je n'ai pas encore regardé ton fichier, mais j'ai une première remarque. Pourquoi tu passes par un format texte ? Pour les nombres, il est plus logique que l'utilisateur rentres naturellement un nombre en utilisant le séparateur de décimal habituel de son PC et tu peux le mettre au format que tu souhaites avant de le transformer en TXT. Pareil pour les dates, ça va être un casse tête si tu veux forcer un format prédéfini. Au pire tu mets la colonnes dans un format avec le mois en texte pour qu'il puisse se rendre compte s'il a inversé mois et jour.

**jakomu** · 07/12/2011, 17h07

Bonjour Zebreloup,

En fait, tout ceci est dans le but d'importer les donner dans une base oracle.
J'ai donc des impératifs de mise en forme.

J'ai testé le truc dans tous les sens, si les cellules ne sont pas au format texte à la base, c'est la mouise...

Pour "démystifier" mon projet, je propose de vraiment le décortiquer, en procédant par étapes simples.

1 - trouver la dernière ligne de mon tableau ?

Qui saura, qui sauraaaaa, qui sauraaaaaaaaaaaa...

J'ai réussi à trouver mon n° de derniere ligne !!!
J'ai aussi réussi à virer ces conneries d'espaces avant et aprés !!!

Savez vous comment :

- virer les "doubles espaces" présents dans les expressions ?

- verifier si cell.value se trouve bien dans un tableau d'un autre onglet ?

Merci !

**ZebreLoup** · 07/12/2011, 17h07

Edit : Je suis un peu en retard pour la première question mais quand même :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Dim ws As Worksheet
Set ws = Worksheets("ma feuille")
 
Dim lastRow As Long
lastRow = ws.UsedRange.Rows.Count

Pour l'histoire du format texte, il suffit ensuite d'utiliser la fonction Format en VBA.

Pour le remplacement, si monRange a été défini auparavant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
monRange.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Pour l'autre point, de même que tu as RECHERCHEV ou EQUIV en formules, tu as en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WorksheetFunction.VLookUp(...)
WorksheetFunction.Match(...)

**jakomu** · 07/12/2011, 18h55

Merci beaucoup !

Autre question, comment ecrire mon test vlookup en faisant référence à Worksheets(Manufacturers).Range("D58: D434") par exemple ?

J'ai systématiquement l'erreur "Subscript out of range" ??!

**ZebreLoup** · 07/12/2011, 18h59

Il manquait peut-être les guillemets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Manufacturers").Range("D58:D434")

**jakomu** · 07/12/2011, 20h06

Problème :

Savez-vous comment contrôler que la valeur d'un champ ne dépasse pas X caractères ?

Aussi, est-il possible de contrôler que la valeur du champ soit bien composée de trois chiffres par exemple ?

J'aimerai mettre en place ces deux tests avec mise en forme conditionnelle.

Enfin, comment faire un test sur la présence de deux valeurs dans ce type de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
For Each Cell In Range("F2:F" & Dern & "")
If Cell.Value = "" Or IsError(Application.Match(Cell.Value, Program, 0)) Then
Cell.Interior.Color = RGB(255, 0, 0)
End If
Next

Je voudrais checker qu'une valeur rentrée soit bien en rapport avec le contrat concerné. J'ai en référence, une table dans un autre onglet avec en colonne A:le contrat et en colonne B:les valeurs possibles ???

Merci d'avance !

**kiki29** · 08/12/2011, 10h58

Salut, adaptation à poursuivre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
    For Each cell In Range("F2:F" & dern)
        If Len(cell) = 0 Or cell.Value > 999 Then
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell

Voir également aide en ligne sur Like

**jakomu** · 08/12/2011, 11h43

Bonjour Kiki29, merci !!!
J'ai adapté, c'est pile poil ce qu'il me fallait. Je ne sais pas pourquoi, je cherchais compliqué...

Du coup j'en profite :

J'ai dans ma colonne Q j'ai une donnée qui peut prendre plusieurs valeurs en fonction de la valeur en A. J'ai dans un autre onglet la table de référence A/Q.

Comment coder ça...???
Je ne vois pas comment faire ça avec match. Je pense qu'il faudrait stocker les deux valeurs dans une sorte de tableau, ensuite parcourir ma table de référence et tester si une occurrence est trouvée...?

Merci !

**ZebreLoup** · 08/12/2011, 12h13

Si tu connais la fonction Excel RechercheV (ou VLookUp en anglais), sache que tu peux l'utiliser en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.WorksheetFunction.VLookUp(...)

Si tu ne la connais pas, regarde l'aide d'excel ou cherche sur ce site ou google.

**jakomu** · 08/12/2011, 13h02

Au risque de paraître ridicule, je ne visualise pas la syntaxe appropriée pour le type de recherche que je souhaite faire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Comment adapter ceci à mon exemple ? Je veux que la valeur de ma colonne Q soit bien cohérente à celle de la colonne A. La table de réf pour ces deux valeurs se trouvant dans un autre onglet.

...

**ZebreLoup** · 08/12/2011, 13h08

Imaginons que ta table de correspondance est dans une feuille tabCorres au niveau A1:B100. Pour chaque ligne i concernée sur la feuille Feuil1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Dim ws As Worksheet
Set ws = Worksheets("Feuil1")
 
Dim rgCorres As Range
Set rgCorres = Worksheets("tabCorres").Range("A1:B100")
 
ws.Cells(i, "Q").Value = WorksheetFunction.VLookup(ws.Cells(i, "A").Value, rgCorres, 2, False)
'2 pour le deuxième colonne de la ligne sur laquelle il a trouvé la valeur en première colonne
'False pour une recherche exacte. Reste la gestion du cas où il ne trouve rien

**jakomu** · 08/12/2011, 13h52

Ok, je commence à comprendre...

Justement, en gros je ne veux gérer que le cas ou il ne trouve rien.

J'ai ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Set CU = ActiveWorkbook.Worksheets("CU").Range("A2:B576")
 
For m = 2 To Dern
    For Each cell In Range("Q2:Q" & m & "")
        If Range("A" & i & "").Value = "" Or IsError(Application.VLookup(Feuil1.Cells(m, "A").Value, CU, 2, True)) Then
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.ColorIndex = xlNone
    End If
    Next
Next

En fait, ça passe bien sauf que mes champs finissent systématiquement en rouge, même si la valeur est correcte par rapport à la colonne A.

Je ne suis pas certain de ce que renvoie Vlookup, c'est pourquoi mon test est peut être mauvais...
Je ne suis pas certain non plus d'avoir saisi à quoi servait le 2 ?
"la deuxième colonne de la ligne sur laquelle il a trouvé la valeur en première colonne"

Merci de votre aide.

**ZebreLoup** · 08/12/2011, 15h11

Je ne comprends pas bien ta double boucle (pour chaque ligne, tu boucles sur toutes les cellules de la colonne jusqu'à cette ligne. Si tu as 100 lignes, tu vas faire 100 fois la même chose pour la première ligne, 99 fois pour la deuxième...). Tu utilises également i qui sort d'on ne sait où...

Pour la compréhension de VLookup, si tu as un Excel Français, essaie de mettre en Q2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV($A2;CU!$A$2:$B$576;2;FAUX)

Et de dérouler sur ta colonne Q cette formule.

En anglais, tu remplaces RECHERCHEV par VLOOKUP et les ; par des ,

cette fonction va rechercher dans la première colonne de la plage CU!A2:B576, la valeur qui est en A2. S'il la trouve, il va chercher sur la même ligne, la valeur située dans la colonne i (ici 2) de la plage en question. Le dernier paramètre précise si la recherche est approchée ou pas

**jakomu** · 08/12/2011, 15h27

Le "i" n'avait effectivement rien à faire là.

Pour ce qui est du procédé, je parcours chaque colonne de la ligne 2 à la dernière ligne en appliquant une mise en forme conditionnée par le résultat des tests.

Si j'ai tout bien saisi, mon test devrait s'écrire comme ce qui suit ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
For m = 2 To Dern
    For Each cell In Range("Q2:Q" & m & "")
        If Range("A" & m & "").Value = "" Or IsError(Application.VLookup("A" & m & "", "CU!A2:B576", 2, False)) Then
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.ColorIndex = xlNone
    End If
    Next
Next

Ça passe toujours mais ne fonctionne pas. Les cellules sont systématiquement colorées, mon test pue du cul...

**ZebreLoup** · 08/12/2011, 15h39

Avec ce que tu fais, toute ta colonne Q n'est conditionnée qu'à la dernière valeur sur la colonne A :
Pour chaque ligne m, si valeurs en A répond au critère, colorier toutes les cellules de la colonne Q entre 1 et m en rouge !

Essaie plutôt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
    For Each cell In Range("Q2:Q" & Dern & "")
        If cell.Offset(0, -15).Value = "" Or IsError(Application.VLookup(cell.Offset(0, -15), Worksheets("CU").Range("A2:B576"), 2, False)) Then
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.ColorIndex = xlNone
    End If
    Next

l'exemple que je t'avais mis plus haut pour le Vlookup était pour une formule dans une feuille excel, pas dans le VBA. Ici, il faut vraiment travailler sur les range (enfin c'est plus propre).

**jakomu** · 08/12/2011, 16h05

Bon...je dois être abruti, rien ne fonctionne. Enfin ça tourne mais ne donne pas le résultat escompté...

Tu veux bien qu'on reprenne le fichier que j'ai posté en exemple ?

En fait, je voudrais vérifier que les valeurs de la colonne bzu dans feuil1 soient ok par rapport aux valeurs de la colonne1 dans feuil1 en prenant le tableau de l'onglet bzu en référence.

Si la valeur est ok par rapport à colonne1 ==> nada
Si pas ok ==> colorée rouge

Merci de ta patience, je comprends vite mais il faut m'expliquer longtemps...

**ZebreLoup** · 08/12/2011, 16h33

Il y a plusieurs façons de faire, mais voici un code qui marche chez moi sur ton fichier :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Option Explicit
 
Public Sub test()
    Dim rgBase As Range
    Dim ws As Worksheet
    Dim c As Range
    Dim lastRow As Long
    Dim test As Variant
    Dim valeurRef As String
 
    Set rgBase = Worksheets("bzu").Range("A2:B12")
    Set ws = Worksheets("Feuil1")
 
    lastRow = ws.Range("J65536").End(xlUp).Row
 
    For Each c In ws.Range("J2:J" & lastRow)
        valeurRef = c.Offset(0, 1 - c.Column).Value
        test = Application.VLookup(c.Offset(0, 1 - c.Column), rgBase, 2, False)
        If IsError(test) Then valeurRef = "": test = ""
 
        If valeurRef = "" _
        Or test <> c.Value Then
            c.Interior.Color = RGB(255, 0, 0)
        Else
            c.Interior.ColorIndex = xlNone
        End If
    Next c
 
End Sub