Discussion :

[SuperBug]

Bonjour!

Je suis un étudiant en plein stage et j'aurais besoin d'aide!

Il y a 2 questions que j'aimerais poser :
- Premièrement, j'ai un classeur excel composé d'environ 35000 lignes, et je dois faire une comparaison entre deux colonnes A et B (comparer 2 références)
puis inscrire "O" ou "N" dans une autre colonne C, si la référence en A et la meme qu'en B. Pour se faire, je fais ceci :

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
    Dim finColDistrib As Long, finColOT As Long, i As Long
    Dim res As Variant, search As Variant
    Dim rng As Range
 
    Worksheets("Feuil1").Activate
 
    finColOT = Cells(Rows.Count, "B").End(xlUp).Row
    finColDistrib = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("B3:B" & finColOT)
 
    For i = 3 To finColDistrib
        search = Range("A" & i)
        Set res = rng.Cells.Find(What:=search, LookAt:=xlWhole)
        If (res Is Nothing) Then
            Range("C" & i) = "N"
        ElseIf (res = search) Then
            Range("C" & i) = "O"
        End If
    Next i

Le problème est que le temps d'exécution est vraiment trop long ... Est-il possible d'optimiser un peu svp?

- Deuxièmement, je copie une colonne avec une formule d'un classeur Class1 dans un autre classeur Class2, j'aimerais copier seulement la formule dans le Class2. Ca j'ai réussi :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
    Dim classeurSrc As Excel.Workbook
    Set class1 = Workbooks.Open(cheminSrc & "Class1")
 
    class1.Worksheets("Feuil1").Activate
    finCol = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A2:A" & finCol).Copy
    DisplayAlerts = False
    class2.Worksheets("Feuil1").Range("A2").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,   Transpose:=False
    DisplayAlerts = True
 
    class2.Save
    class1.Close

Le problème est que, dans la formule, on a besoin des données qui se trouvent dans une autre feuille du classeur, et que donc, quand je copie la formule dans Class2, la formule va chercher les données nécessaires dans la Feuil2 de Class1 qui est le classeur d'ou je viens de copier la colonne... Il faudrait que la formule aille chercher les données dans la Feuil2 de Class2. Du coup, comment dois-je faire?

Je vous remercie d'avance!
Cordialement!

[MacKay77]

Bonjour !

1) Les références sont-elles dans la même ligne? Si c'est le cas tu pourrais juste comparer le contenu de deux cellules à chaque boucle.
Sinon tu peux toujours en cadrer ton code par

Code :

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

1
2
3
Application.ScreenUpdating = False
'ton code
Application.ScreenUpdating = True

Ca désactivera jusqu'à la fin de l’exécution du code l'affichage ce qui pourrait avoir un impact pour un affichage de 35 000 lignes.

2)
As-tu essayé la fonction indirect() dans ta formule?
En passant l'argument transpose à true?

[SuperBug]

Hey!

Huuum je vais essayer le coup de l'affichage désactivé, je te dis si ça améliore quelque chose ou non!
Les références ne sont pas sur la même ligne, en fait, la colonne A fait environ 200 lignes, tandis que la B en fait 35000. Je compare la référence de la colonne B avec toutes celles présentes en A. Si Find trouve la référence de la colonne B dans la colonne A, je mets "O" en C, sinon je mets "N"

Et je ne connais pas la formule indirect(), pourrais tu m'en dire plus s'il te plait?

En tout cas, merci de ta réponse si rapide!

[RyuAutodidacte]

Bonjour,
tu peux essayer avec avec la méthode CountIf
un exemple à adapter :

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub ComparaisonOuiNon()
Set DataSource = Feuil2.Range("I3:I8")
DataCible = Feuil2.UsedRange.Columns(1)
Columns(2).Resize(UBound(DataCible)) = "NON"
For i = 1 To UBound(DataCible)
    If WorksheetFunction.CountIf(DataSource, DataCible(i, 1)) = 1 Then
        Range("B" & i).Value = "OUI"
    End If
Next
End Sub

Donc bien sur il faut faire le bon pointage et le bon ciblage

Edit : Hi @joe.levrai on s'est croisé dans la réponse

[MacKay77]

Hey!
Et je ne connais pas la formule indirect(), pourrais tu m'en dire plus s'il te plait?

En tout cas, merci de ta réponse si rapide!

Citation de l'aide d'office :

Renvoie la référence spécifiée par une chaîne de caractères. Les références sont immédiatement évaluées afin d’afficher leur contenu. Utilisez la fonction INDIRECT lorsque vous voulez modifier la référence à une cellule à l’intérieur d’une formule sans modifier la formule à proprement parler.

Je ne l'ai jamais testé mais ça a l'air de faire ce que tu souhaitais

[SuperBug]

@Mackay77 , Merci du tuyau! Je vais regarder ca plus en profondeur

[RyuAutodidacte]

Tiens mets ce code dans un module dans un nouveau classeur tout va se faire automatiquement pour une petite démonstration
le tableau Toto insère les valeurs à checker en col I => pour toi ça sera en colonne B (quelque soit le nombre d'éléments à vérifier ça ne posera pas de pbm)
le sub copie n'est là que pour mettre en place les fausses données

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
Sub Copie()
Dim i As Long, Toto
Application.ScreenUpdating = False
    For i = 1 To 35000
        Cells(i, 1) = i
    Next
    Toto = Array(1, 5, 10, 35, 35000, 25, 75, 12, 150, 20000, 22000)
    Cells(1, 9).Resize(UBound(Toto)).Value = Application.Transpose(Toto)
    ComparaisonOuiNon
Application.ScreenUpdating = True
End Sub
 
Sub ComparaisonOuiNon()
Set DataSource = Feuil1.Range("I1:I10")
DataCible = Feuil1.UsedRange.Columns(1)
Columns(2).Resize(UBound(DataCible)) = "NON"
For i = 1 To UBound(DataCible)
    If WorksheetFunction.CountIf(DataSource, DataCible(i, 1)) = 1 Then
        Range("B" & i).Value = "OUI"
    End If
Next
End Sub

Pour toi dans ta colonne A tu auras tes données
Dans ta colonne B du même classeur les données récupérer du second classeur soit par une rechercheV ou autre
En Col C les réponses OUI/NON
Mais bien sur tout cela est à adpater

[joe.levrai]

Bonjour,

pour la comparaison de tes deux colonnes, tu peux utiliser une formule (éventuellement par VBA, tu trouveras sur le site des billets de blog qui en expliquent le fonctionnement)

voici un exemple "format Excel" où je n'ai pas optimisé la plage utile de la colonne B
Cette formule est placée en C1, et si je l'étire sur ma colonne C, elle fonctionne pour chaque ligne

=SI(NB.SI($B:$B;A1)>0;"O";"N")

Astuce : si tu passes par une formule relative (.FormulaR1C1), tu auras la possibilité, sans aucune boucle, d'appliquer ta formule sur l'ensemble de ta plage.
Un conseil pour apprendre : enregistre une macro où tu écris cette formule en C1. Tu obtiendras la méthode R1C1 dont j'ai parlé. Il faudra adapter pour effectuer l'application sur toute ta colonne


pour la copie des formules ... ne serait-il pas plus pratique de les écrire directement dans ta feuille de destination, plutôt que de les copier pour ensuite en modifier les références ?
A quoi ressemble cette formule d'ailleurs ?
Par ailleurs, la propriété .Formula va te retourner la formule présente dans une cellule. Tu peux très bien "remplacer" la référence voulue avec une fonction Replace() puisque tu disposeras de la formule sous forme de chaine de caractère

[SuperBug]

Bonjour Joe.Levrai

J'aurais du commencer par ça oui! Voici la formule :

Code :

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

    =RECHERCHEV(K195;'Feuil2'!$A$2:$B$16;2;FAUX)

Si j'ai décidé de copier la colonne avec la formule toute prête, c'est parce que, quand je créais la formule au fur et à mesure pour chaques cellules (il y a 35000 lignes je rappelle), l'exécution continuais toujours au bout de 30 minutes ... C'est beaucoup trop long ...
Il faut aussi que tout se fasse automatiquement (consigne donnée par mon tuteur), et donc je ne peux pas étendre la formule à la main.

Avec .FormulaR1C1, j'avais déja essayé, mais je n'avais pas compris le fonctionnement ...

Et comment marche replace()? Désolé pour le dérangement ^^'

Merci de ta réponse

[RyuAutodidacte]

voilà un moyen de copier ta formule sur toutes les lignes d'un coup (à adapter)
(Attention bien noter dans la formule la partie relatif ou absolue dans l'adresse)

Code :

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

1
2
Feuil1.Range("F3").FormulaLocal = "=$E3-$D3" 'Remplacer par ta formule
Feuil1.Range("F3").Copy Destination:=Feuil1.Range("F3:F8")' pour la copier sur la plage voulue => à définir

[SuperBug]

Hey @RyuAutodidacte !

Je vais essayer ton exemple

D'ailleurs, @MacKay77 , je suis désolé, mais ScreenUpdating = False/True ne change pas grand chose Le temps d'exécution est toujours aussi long

[DeathZarakai]

Bonjour,

Si tu tiens à utiliser une macro je te suggère de faire en 2 tours avec un dictionnaire:

Code :

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

1
2
3
4
Set MonDico = CreateObject("Scripting.Dictionary")
For i = 3 To finColOT
     If Not MonDico.Exists(CStr(Cells(i, 2).Value)) Then MonDico.Add CStr(Cells(i, 2).Value), ""
Next i

Ensuite tu boucle pour vérifier :

Code :

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

1
2
3
4
5
6
7
For i = 3 To finColDistrib
     If MonDico.Exists(CStr(Cells(i, 1).Value)) Then
          Range("C" & i) = "O"
     Else
          Range("C" & i) = "N"
     End If
Next i

Même sur 35k lignes cela devrait être rapide.

Je suis désoler je ne peux pas plus explicité maintenant je dois partir en entretien d'embauche.

Bon courage et hésite pas à te documenter sur le Web ya plein de bonnes explications du dico.

DeathZarakai.