[XL-2003] Macro pour check données et format + génération TXT (tab separator)

[jakomu]

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]

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]

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]

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

Code :

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 :

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 :

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

[jakomu]

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]

Il manquait peut-être les guillemets :

Code :

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

[jakomu]

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 :

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]

Salut, adaptation à poursuivre

Code :

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]

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]

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

Code :

Application.WorksheetFunction.VLookUp(...)

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

[jakomu]

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

Code :

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]

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 :

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]

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 :

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]

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 :

=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]

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 :

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]

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 :

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]

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]

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

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
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

[jakomu]

ça marche...


ZebreLoup.

Merci.

[ZebreLoup]

Allez regarde bien ce que je t'ai donné, tu verras qu'il manque un .Column (Pour faire un offset jusqu'à la première colonne en fait). Bière ?