Discussion :

[phiphi_j]

Bonjour,

J'ai un tableau excel pour lequel je dois utiliser toutes les lignes(65536).
Mon problème est que j'avais créé une formule recherche pour remplir ma colonne mais l'ordinateur n'arrive pas à suivre et plante à chaque fois.

Je cherche quelle formule il faut utiliser pour que si ma ligne a une valeur > 0 dans la colonne A alors dans la colonne P j'utilise la fonction suivante

Code :

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

=SI(OU(A2="";ESTNA(RECHERCHEV(A2;docdest;3;0)));"";RECHERCHEV(A2;docdest;3;0))

alors mettre la valeur et si la valeur dans la colonne A est = 0 alors la boucle s'arrête.
Je dois remplir 26 colonnes de cette façon.
A moins que quelqu'un ait une autre solution beaucoup plus efficace à me proposer et surtout plus rapide...

En plus de ce soucis de plantage à cause du poids des infos, j'ai un problème de format de données.
Ma table docdest est un copier/collage spécial avec liaisons d'un autre fichier mais le problème est que ma formule me donne des messages d'erreur #N/A parce que certaines données de ce tableau sont des chiffres au format lettre (j'ai voulu convertir tout le tableau mais il y a aussi des données lettres dedans alors je peux pas).
J'ai essayé de faire un copier/collage spécial : valeur mais ça marche pas non plus.
Comment dois-je faire pour ne plus avoir de message d'erreur ?
Merci

[LeForestier]

Bonjour,
en premier lieu, résorber les erreurs qui sont dans les cellules et traiter un problème après l'autre et pas tous ensemble, sinon,ça marchera jamais.
Explique comment ça se fait que tu a des erreurs dans ta colonne.
De plus tu montre une formule excel et tu demande de l'aide en VBA ? qu'en est-il ?
A+

[LeForestier]

Réponse sur le forum et pas en MP Svp, comme celà, tout le monde peu t'aiguiller et être au courant de ton problème.
Mais que cherche-tu, une solution en VBA ou avec des formules excel, auquel cas tu ferais mieux de poser ta question dans le forum Excel. Bien que je pense que tu devras passer par du VBA.
A+

[phiphi_j]

En fait, je ne sais pas pourquoi mais alors que deux de mes onglets sont des copier/collage spécial avec liaisons du même document excel avec, il y en a un dont le format est parfait sans y toucher et l'autre, dont ma table docdest est issue, qui n'a pas le même format et qui a des petits triangles verts sur toutes les cellules contenant des chiffres uniquement et pas des chiffres et des lettres. Si j'en parle là c'est parce que c'est en cherchant une boucle pour appliquer ma fonction recherche à toute la colonne que j'ai vu apparaitre des #N/A.

extrait du début de boucle que j'ai fait:

Code :

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

1
2
3
4
5
6
7
8
'boucle de formule de recherche des colonnes:
'p:
    Dim i As Long
    For i = CLng(2) To CLng(10)
    If Range("a" & i) > "0" Then
    Range("P" & i).Value = "=IF(COUNTIF(docdest!R2C1:R65536C1,fact!RC[-15])>0,VLOOKUP(fact!RC[-15],docdest,3,0),"""")"
    End If
    Next i

Mais je voudrais qu'elle s'arrête de calculer quand la valeur 0 est dans la colonne A. Des suggestions ?

[Krovax]

Bonsoir,
Quand tu donne un code utilise les balise
sélectionne le code et clique sur l'icone # au dessus de la zone d'édition

Si tu veux quitter la boucle for il suffit de luio dire exit for

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim i As Long
For i = CLng(2) To CLng(10)
If Range("a" & i) > 0 Then
Range("P" & i).Value = "=IF(COUNTIF(docdest!R2C1:R65536C1,fact!RC[-15])>0,VLOOKUP(fact!RC[-15],docdest,3,0),"""")"
elseif If Range("a" & i) = 0 Then
exit for
'ou alors pour quitter la subroutine
exit sub
End If
Next i

[phiphi_j]

Merci Krovax pour la formule de sortie mais par contre j'ai pas compris ton histoire de balise et de #. Je me suis mise au visual basic depuis seulement une semaine alors il y a plein de choses que j'ignore.

[Krovax]

Je ne parlais pas de visual basic mais du forum.

Regarde la différence tu avais mis quelque chose comme
(Merci au modérateur de ne pas éditer le code, sinon ma démonstration va perdre un peu de son sens )

Dim i As Long
For i = CLng(2) To CLng(10)
If Range("a" & i) > 0 Then
Range("P" & i).formula= "=IF(COUNTIF(docdest!R2C1:R65536C1,fact!RC[-15])>0,VLOOKUP(fact!RC[-15],docdest,3,0),"""")"
elseif Range("a" & i) = 0 Then
exit for
'ou alors pour quitter la subroutine
exit sub
End If
Next i


et moi quelque chose comme

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim i As Long
For i = CLng(2) To CLng(10)
   If Range("a" & i) > 0 Then
      Range("P" & i).Value = "=IF(COUNTIF(docdest!R2C1:R65536C1,fact!RC[-15])>0,VLOOKUP(fact!RC[-15],docdest,3,0),"""")"
   elseif Range("a" & i) = 0 Then
      exit for
      'ou alors pour quitter la subroutine
      exit sub
   End If
Next i

(bon j'ai rajouté l'indentation pour faire plus jolie)

Avoue que la deuxième version est beaucoup plus simple a lire.
Du coup les modérateur sont obligé de venir éditer ton message pour rendre ton code lisible

Alors pour savoir comment faire clique ici

ps au passage pour mettre une formule il faut un .formula et non un .value j'ai modifié le code

[LeForestier]

Re,

Je cherche quelle formule il faut utiliser pour que si ma ligne a une valeur > 0 dans la colonne A alors dans la colonne P j'utilise la fonction suivante

A la place de "j'utilise la fonction suivante " dis plutôt ce que tu veux mettre dans la colonne P. Il ne sera probablement pas nécessaire de mettre une formule mais simplement le résultat !

[phiphi_j]

J'ai résolu le problème format de mes données mais quand je lance ma macro, si je regarde comme ça rien ne s'affiche, si je vais sur une des cellules de la colonne la formule apparait mais moi je voudrais que ce soit le résultat de la fonction recherche qui apparaisse. Comment faire ?

[Krovax]

est ce que tu utilise bien le .formula?
montre nous la dernière version de ton code (avec les balise )

[aalex_38]

que ce soit le résultat de la fonction recherche qui apparaisse. Comment faire ?

Une autre piste, lance cette macro :

Code :

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

1
2
3
4
5
Sub test-calcul ()
ActiveSheet.EnableCalculation = True
Calculate
Application.ScreenUpdating = True
End sub

ou va dans OUTIL/OPTION/CALCUL et vérifie que tu est bien en automatique

[phiphi_j]

J'ai aussi résolu le problème de l'affichage du résultat. Mai là je viens de lancer ma macro juste sur la première colonne ( P) et l'ordi met au moins 10 minutes à effectuer ma fonction recherche avec la boucle. connaissez-vous un moyen pour que cela ne prenne que quelques secondes parce que j'ai encore 26 colonnes à renseigner de la même façon et le but des macros étant quand même d'accélérer les manip, là ça vaut pas le coup. Mais j'en ai vraiment besoin...

[Krovax]

Montre nous a macro complète car il doit y avoir pas mal de truc à améliorer pour que se soit si long

Sinon essaye de commencer ta macro par

Code :

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

1
2
    Application.ScreenUpdating = False
    ActiveSheet.EnableCalculation = False

et de finir par le code d'aalex

Code :

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

1
2
3
 ActiveSheet.EnableCalculation = True
Calculate
Application.ScreenUpdating = True

[phiphi_j]

J'ai fait une commande comme ça pour chaque colonne (seul la clé et la colonne de recherche change) mais comme il y en 26 je vous en met qu'une. Seules les 2 premières sont très longues après j'ai pas testé mais ça devrait être moins long parce qu'il y a de moins en moins de données dans mes colonnes au fur et mesure que j'avance dans le tableau. Vous allez me dire que 65536 c'est beaucoup mais je dois prévoir un espace suffisant pour que l'entreprise n'est pas à y toucher trop vite. De plus dans ma colonne A qui sert de clé pour mes fonctions recherches il y a déjà plus de 3000 lignes.

Code :

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

1
2
3
4
5
6
7
8
Dim i As Long
    For i = CLng(2) To CLng(65536)
    If Range("a" & i) > "0" Then
    Range("o" & i).Formula = "=IF(OR(RC[-14]="""",ISNA(VLOOKUP(RC[-14],docdest,3,0))),"""",VLOOKUP(RC[-14],docdest,3,0))"
    ElseIf Range("a" & i) = 0 Then
    Exit For
    End If
    Next i

Si vous trouvez une solution pour que ça aille plus vite ...

[Krovax]

Et si au lieu d'aller jusqu'a la ligne 65535 tu t'arrettais a la dernière ligne de la colonne? Ca te ferasi peut être gagner du temps

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
Dim i As Long
    Application.ScreenUpdating = False
    ActiveSheet.EnableCalculation = False
 
 
    For i = 2 To Range("A65536").End(xlUp).Row 'excel est malin il comprend que 2 est un nombre
    If Range("a" & i) > "0" Then
    Range("o" & i).Formula = "=IF(OR(RC[-14]="""",ISNA(VLOOKUP(RC[-14],docdest,3,0))),"""",VLOOKUP(RC[-14],docdest,3,0))"
    ElseIf Range("a" & i) = 0 Then ' a mon avis ceci devient inutil mais je ne peux pas savoir si un zéro peu apparaitre dans une colonne
    Exit For
    End If
    Next i
 
'le reste de ton code
 ActiveSheet.EnableCalculation = True
Calculate
Application.ScreenUpdating = True

Après tu peux aussi t'arréter a la dernière colonne
Regarde ce lien il pourrais t'aider
http://www.developpez.net/forums/d45...gnees-feuille/

[phiphi_j]

Merci pour le lien, très instructif et pour tes explications. Juste une question, au lieu de ne plus avoir de données dans ma colonne A ce sont des 0 qui apparaissent, c'est pour ça que j'ai mis un = 0 pour mon elseif :

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
12334
12335
12318
12319
12320
12321
12322
0
0
0
0
0
0
0
0
0

Est-ce que je peux quand même utiliser la formule que tu m'as donnée ?

[Krovax]

ha non dans ce cas ca ne fonctionnera pas désolé (mais tu peux quand même virer les Clng )
Concerve quand même les

Code :

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

1
2
    Application.ScreenUpdating = False
    ActiveSheet.EnableCalculation = False

Car si tu ne désactive pas le recalcule automatique a chaque modification tu recalcule toutes les formule et comme tu as de plus en plus de formule c'est de plus en plus lent. cqfd (enfin je crois )
Et ne transforme pas le 0 en chaine de caractère avec des guillement laisse le tranquille (j'ai oublié de modifier le premier dans mon code)

Code :

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

If Range("a" & i) > 0 Then

Code :

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

1
2
3
4
5
6
7
8
Dim i As Long
For i = 2 To 65536
    If Range("a" & i) > 0 Then
       Range("o" & i).Formula = "=IF(OR(RC[-14]="""",ISNA(VLOOKUP(RC[-14],docdest,3,0))),"""",VLOOKUP(RC[-14],docdest,3,0))"
    ElseIf Range("a" & i) = 0 Then
       Exit For
    End If
Next i

[phiphi_j]

Merci beaucoup à tous ceux qui m'ont aidée, ça y est ma macro fonctionne et relativement vite !