Petite aide sur cette macro de contrôle

Version imprimable

Bonjour,

Comment modifier cette macro pour commencer à contrôler à partir de la 2ème ligne et non pas de la 1eme ligne (en tête du tableau)??

Et autre chose il lui Faudrait lui dire contrôler onglet feuille "BDD" et non pas elle contrôle la feuille active. Comment faire??

merci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Sub atester()
Application.ScreenUpdating = False
Colonne = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL")
NBRCAR = Array(10, 1, 22, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 3, 255, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 4, 255, 8, 3, 11, 40, 11, 11, 11, 3, 10, 10, 3, 8, 8, 10, 3, 100, 100, 100, 100, 100, 100, 8, 7, 40, 40, 3, 10, 10, 255, 255, 255, 5, 1, 255, 333, 20, 20, 30, 20, 20, 20, 20, 20, 20, 20, 30, 30, 20, 20, 20, 20, 20, 20, 30, 30, 1, 1, 100, 100, 100, 100, 100, 100)
With Sheets("catalogue")
For i = 1 To 90
For j = 0 To 89
If Not IsEmpty(Cells(i, Colonne(j))) Then
If Len(Cells(i, Colonne(j))) > NBRCAR(j) Then
Sheets("Feuil2").Range("T65536").End(xlUp).Offset(1) = Cells(i, Colonne(j)).Address(0, 0)
End If
End If
Next
Next
Application.ScreenUpdating = True
End With
 
End Sub

merci de l'aide

24/04/2009, 11h07
Ptikir
Salut pour ta première question (commencer à partir de la deuxième ligne)

J'utiliserais une des deux solutions ci dessous :

Soit tu utilises une condition qui ne prend pas en compte la valeur 1
Code:

1 2 3 4 5 dim i as integer 'compteur de ligne (je sais pas lequel tu utilises) if i > 1 then 'ton code end if
Ou alors tu utilise une boucle
Code:

1 2 3 4 5 6 dim i as integer for i = 2 to 65536 'ton code next i
Pour ta deuxième question je ne suis pas sur de la comprendre....

Mais d'après ce que je comprends tu fais le control sur la feuille "catalogue". Donc au lieu de mettre :
With Sheets("catalogue")
Met
With Sheets("BDD")

A+

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub atester()
Application.ScreenUpdating = False
Colonne = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL")
NBRCAR = Array(10, 1, 22, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 3, 255, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 4, 255, 8, 3, 11, 40, 11, 11, 11, 3, 10, 10, 3, 8, 8, 10, 3, 100, 100, 100, 100, 100, 100, 8, 7, 40, 40, 3, 10, 10, 255, 255, 255, 5, 1, 255, 333, 20, 20, 30, 20, 20, 20, 20, 20, 20, 20, 30, 30, 20, 20, 20, 20, 20, 20, 30, 30, 1, 1, 100, 100, 100, 100, 100, 100)
With Sheets("BDD")
For i = 2 To 90
For j = 0 To 89
If Not IsEmpty(Cells(i, Colonne(j))) Then
If Len(Cells(i, Colonne(j))) > NBRCAR(j) Then
Sheets("Feuil2").Range("T65536").End(xlUp).Offset(1) = Cells(i, Colonne(j)).Address(0, 0)
End If
End If
Next
Next
Application.ScreenUpdating = True
End With
 
End Sub

EDIT : le compteur proposé dans la réponse précédente est déjà présent 8O

Merci à vous,
pour ma 1ere ca marche, parcour la 2eme en inserant with sheets ("BDD"), lancant la macro de onglet "Feuil2" pour controler l'onglet "BDD". Ca va me contrôler Onglet feuil 2
Je comprend pas??

Sinon autre chose, pour i = 2 To 15000, on peux pas faire i = 2 to aller jusqu'à derniere donné au bas de la colonne???

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub atester()
Sub atester()
Application.ScreenUpdating = False
Colonne = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL")
NBRCAR = Array(10, 1, 22, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 3, 255, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 4, 255, 8, 3, 11, 40, 11, 11, 11, 3, 10, 10, 3, 8, 8, 10, 3, 100, 100, 100, 100, 100, 100, 8, 7, 40, 40, 3, 10, 10, 255, 255, 255, 5, 1, 255, 333, 20, 20, 30, 20, 20, 20, 20, 20, 20, 20, 30, 30, 20, 20, 20, 20, 20, 20, 30, 30, 1, 1, 100, 100, 100, 100, 100, 100)
With Sheets("BDD")
For i = 2 To 15000
For j = 0 To 89
If Not IsEmpty(Cells(i, Colonne(j))) Then
If Len(Cells(i, Colonne(j))) > NBRCAR(j) Then
Sheets("Feuil2").Range("T65536").End(xlUp).Offset(1) = Cells(i, Colonne(j)).Address(0, 0)
End If
End If
Next
Next
Application.ScreenUpdating = True
End With
 
 
End Sub

petit erreur à corriger

bonjour,

Il faut ajouter le point devant l'objet

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Sub atester()
Application.ScreenUpdating = False
Colonne = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL")
NBRCAR = Array(10, 1, 22, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 3, 255, 40, 800, 4, 255, 40, 800, 4, 255, 40, 800, 4, 255, 8, 3, 11, 40, 11, 11, 11, 3, 10, 10, 3, 8, 8, 10, 3, 100, 100, 100, 100, 100, 100, 8, 7, 40, 40, 3, 10, 10, 255, 255, 255, 5, 1, 255, 333, 20, 20, 30, 20, 20, 20, 20, 20, 20, 20, 30, 30, 20, 20, 20, 20, 20, 20, 30, 30, 1, 1, 100, 100, 100, 100, 100, 100)
With Sheets("BDD")
     ' Mettre le point pour associer à l'objet Sheets("BDD")
     For i = 2 To 15000
          For j = 0 To 89
              If Not IsEmpty(.Cells(i, Colonne(j))) Then
                 If Len(.Cells(i, Colonne(j))) > NBRCAR(j) Then
                    Sheets("Feuil2").Range("T65536").End(xlUp).Offset(1) = .Cells(i,Colonne(j)).Address(0, 0)
                 End If
              End If
          Next
     Next
Application.ScreenUpdating = True
End With
 
 
End Sub

Re, merci,

Pour i = 2 To 15000 : comment dire prendre de ligne 2 jusqu'à la derniere donnée comme End(xlUp), sans aller jusqu'au bout, pour gagner du tps

merci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 
     For i = 2 To 15000
          For j = 0 To 89
              If Not IsEmpty(.Cells(i, Colonne(j))) Then
                 If Len(.Cells(i, Colonne(j))) > NBRCAR(j) Then
                    Sheets("Feuil2").Range("T65536").End(xlUp).Offset(1) = .Cells(i,Colonne(j)).Address(0, 0)
                 End If
              End If
          Next
     Next
Application.ScreenUpdating = True
End With
 
 
End Sub

24/04/2009, 13h54
DeaD78
Remplace

Code:

For i = 2 To 15000

par
Code:

1 2 For i = 2 to .Range("A65536").End(xlUp).Row
Là, ça sera en fonction de la colonne A mais tu peux changer pour mettre la colonne où il y a le plus de valeurs si jamais il n'y en a pas autant dans chaque colonne.
24/04/2009, 14h29
roidurif
Merci DeaD78 pour ta proposition,

Mais imaginons la derniere donnée de la colonne A est en A16500 et les autres colonnes B17000, C18000 etc....

Ca veut dir que toute les lignes > à 16500 ne seront pas pris en compte???
Code:

1 2 For i = 2 to .Range("A65536").End(xlUp).Row
24/04/2009, 14h49
DeaD78
C'est exactement ça ! Choisis simplement la colonne où y'aura le plus de données et remplace
Code:

1 2 For i = 2 to .Range("A65536").End(xlUp).Row
par
Code:

1 2 For i = 2 to .Range("X65536").End(xlUp).Row 'Tu remplaces X par la colonne la plus grande
Dans le cas où tu ne sais pas laquelle c'est va falloir faire des tests 8O Par exemple, parcourir toutes les colonnes, garder la valeur de la dernière ligne la plus grande et utiliser celle-ci et non une autre. C'est pas compliqué mais, en général, c'est soit autant de valeurs d'une colonne à l'autre soit on connaît la colonne qui sera toujours plus étoffée que les autres.

Bonne chance pour la suite !
24/04/2009, 15h38
roidurif

Merci DeaD78,

J'ai post que je n'arrive pas à resoudre, c'est sur ce liens :

http://www.developpez.net/forums/d73...-texte-saisie/

Tu peux me dire comment obtenir le résultat en colone P12 et non lineaire ligne 6

Cordialement