[XL-2002] Cherche procédure remplacant un Vlookup (recherchev)

[Mirak87]

bonjour

Pourriez-vous m'aider SVP à ameliorer une procédure que je lance pour classer des "bonds" en fonction d'un Code?
Pour cela j'utilise actuellement un Vlookup qui fait appel à une autre feuille du classeur dans laquelle se trouve un liste contenant une classification de ces "bonds".
voila ma macro mais une fois apliquée le fichier devient trop lourd et est difficile à manipuler.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Dim ligne
Function LastRowG()
'feuille active, colonne N
  LastRowG = Cells(Cells.Rows.Count, "N").End(xlUp).Row
End Function
 
sub category
 
'Catégorie
 
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-19],'bonds data new'!C[-26]:C[-13],5,FALSE)"
    Selection.AutoFill Destination:=Range("AA2:AA" & LastRowG)
    Range("AA2").Select
 
end sub

Merci à vous

[jfontaine]

Bonjour,

une solution serait de faire un copier/coller valeur après calcul des vlookup

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sub category
 
'Catégorie
 
Range("AA2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-19],'bonds data new'!C[-26]:C[-13],5,FALSE)"
Selection.AutoFill Destination:=Range("AA2:AA" & LastRowG)
application.calculate
Range("AA2:AA" & LastRowG).Copy
Range("AA2:AA" & LastRowG).PasteSpecial Paste:=xlPasteValues
Range("AA2").Select
 
end sub

[MarcelG]

Bonjour Mirak87, Bonjour Jérôme,

Si je peux me permettre.

Une autre solution serait de traiter la formulation dans sa globalité au niveau des cellules à informer.

D'autre part, plutôt que d'effectuer un collage spécial, il est tout aussi efficace d'utiliser une astuce (de mercatog, je crois) consistant à affecter une valeur à la valeur de la formule.

Ce qui donne (à adapter, notamment en termes d'adressage des champs)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Public Sub ESSAI3()
 
Dim dercel As Range
 
With Sheets(3)
        Set dercel = .Cells(.Rows.Count, 1).End(xlUp)
        With .Range("A1", dercel).Offset(0, 1)
            .Formula = "=VLOOKUP(A1, $I$1:$J$3,2)"
            'astuce collage spécial
            .Value = .Value
        End With
End With
 
Set dercel = Nothing
 
End Sub

[Pierre Fauconnier]

Salut.

Tu pourrais appliquer la formule sur la plage directement, plutôt que de l'appliquer sur la première cellule puis de recopier.

Code :

Range("AA2:AA" & LastRowG).FormulaR1C1="=VLOOKUP(RC[-19],'bonds data new'!C[-26]:C[-13],5,FALSE)"

Pour améliorer éventuellement encore un peu la vitesse d'exécution, tu pourrais désactiver le calcul avant et le réactiver après. Tu pourrais aussi désactiver application.screenupdating et le réactiver en fin de macro.

Evite de sélectionner des cellules. Cela ralentit le système et est souvent superflu. Tu gagneras du temps et de la clarté de code à travailler directement avec les objets Cells ou Range appropriés.

[MarcelG]

Salut Pierre,

Nos méthodes, je pense, sont similaires.

Au demeurant, j'ai testé cette méthode sur 65530 cellules avec un champ de référence sur la même feuille. Le temps d'exécution est de l'ordre de 2 secondes tout au plus.

[Pierre Fauconnier]

Tout à fait, Marcel,

Je n'avais pas vu le "dercell" dans ton code... Mea culpa!

Je pense aussi qu'il faut vraiment de grosses tables de données pour que le vlookup "pagaie dans la semoule"...

[Mirak87]

Bonjour,

Merci pour vos reponse Pierre et Marcel!
effectivement c une base de 65 000 lignes!!

je ne parviens pa à appliquer la copie valeur suggéré en debut de discussion!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Public Sub ESSAI3()
 
Dim dercel As Range
 
With Sheets(3)
        Set dercel = .Cells(.Rows.Count, 1).End(xlUp)
        With .Range("A1", dercel).Offset(0, 1)
            .Formula = "=VLOOKUP(A1, $I$1:$J$3,2)"
            'astuce collage spécial
            .Value = .Value
        End With
End With
 
Set dercel = Nothing
 
End Sub

Pourriez-vous svp me suggérer un code qui copierai toute une feuille sans les formules sur une autre feuille du classeur??

[neupont]

Envoyé par Mirak87

Pourriez-vous svp me suggérer un code qui copierai toute une feuille sans les formules sur une autre feuille du classeur??

Je pense qu'on peut faire ça avec un tableau de variables, qui plus est c'est très rapide :

Code :

1
2
3
4
5
6
Sub test()
Dim mon_tableau()
mon_tableau = Range("A1:A65000").Value
 
Range("C1:C65000") = mon_tableau
End Sub

Ici ça copie la colonne A, où il y aurait des formules, dans la colonne C où il n'y a alors que les valeurs. A adapter bien sûr.


EDIT : je suis très con, il y a même un moyen bien plus simple :

Code :

1
2
3
Sub test()
Range("C1:C2") = Range("A1:A2").Value
End Sub

Ce doit être à peu de chose près la même chose que ce que suggère MarcelG avec .Value = .Value