Discussion :

[esse2016]

Bonjour à tous,

J'ai une base de donnée de 15 colonnes et des lignes dynamiques. C'est à dire que les lignes s'incrémentent au fur et à mesure de la saisie via un formulaire userform.

pour faire simple: mon formulaire est pourvu d'une textbox, la 18, qui peut contenir un nombre à 10 chiffres (au delà j'ai une erreur et je ne sais pas pourquoi alors j'ai limité à 10 chiffres). lorsque je saisis par exemple dans la textbox18 la valeur 1, le code va vérifier dans la base si le nombre 1 existe (Ma base de donnée est telle que les nombres sont classés en ordre décroissant). Si tel est le cas alors affiche le contenu du row (soit les 15 colonnes) dans d'autres textbox's' d'affichage pour IHM. Mais après avoir codé je me rends compte que si ma base s'accroit à 1000 lignes enregistrées alors le séquençage devient long (j'aimerai pouvoir traité jusqu'à 100 000lignes). C'est à dire si je tape 15000 dans mon textbox le temps qui balaye les lignes je peux aller me faire un café. après cette prise de conscience, je me prends maintenant la tête...

comment puis-je rendre ma routine plus fluide dans le traitement de lecture de la base et y afficher le contenu associé selon la ligne saisie dans le champ textbox18 ?
Ma première idée serait de traiter par paquet de ligne. Si ma valeur saisie est 50 alors balaye uniquement la base de 1 à 500. Si ma valeur à présent serait de 4500 alors balaye de 4000 à 4500 mais je ne sais guère par où commencer ? tout ce que je peux comprendre est la "longueur" de traitement en faisant
Range("B9").End(xlDown).Row mais après comment découper ce truc en paquet équitable sachant que ma base s'alimente à chaque saisie, soit en dynamique ?

voici un début de mon code mais pas sur que cela d'une grande aide aux spécialistes. car il n'est pas adapté en cours mais je ne sais pas comment m'y prendre en fait. pour ceux qui on eu la patience de me lire, Ici Ticket correspond à un numéro de ligne en quelque sorte

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
23
24
25
26
27
28
Function recherche(ByVal ticket As Long, ByVal debut As Integer) As String
Dim i, initial, final, facteur As Double
facteur = 10 ^ (Len(TextBox18.Value) - 1)
 
If ticket < 500*facteur Then 'Len(TextBox18.Value) < 4 And Then
initial = Range("B9").End(xlDown).Row - 10 * facteur: final = initial + 10 * facteur
ElseIf ticket > 499 And ticket < 1000 Then
initial = Range("B9").End(xlDown).Row - 1000: final = initial + 1000
ElseIf ticket > 1499 And ticket < 2000 Then
initial = Range("B9").End(xlDown).Row - 1500: final = initial + 1500
End If
 
 
For i = initial To final
 
 
If Cells(i, 2).Value = ticket Then
Me.Controls("TextBox" & debut).Value = Cells(i, 4)
Me.Controls("TextBox" & debut + 1).Value = Cells(i, 6)
Me.Controls("TextBox" & debut + 2).Value = Cells(i, 10)
Me.Controls("TextBox" & debut + 3).Value = Cells(i, 8)
End If
If Cells(i, 2).Value = ticket And (Cells(i, 10).Value = "T" Or Cells(i, 10).Value = "C" Or Cells(i, 10).Value = "N") Then
Me.Controls("TextBox" & debut + 4).Value = Cells(i, 12)
Me.Controls("TextBox" & debut + 5).Value = Cells(i, 14)
recherche = Cells(i, 10)
Exit Function
End If

mais je ne traite pas en dynamique et ma formule ne convient et pas et surtout je voudrais généraliser, si c'est possible, pour éviter des if à gogo pour chaque intervalle. faut il se lancer dans des suites ???

Quelqu'un sait il s'y prendre avec ce genre de problème ?

merci d'avance..

[joe.levrai]

Bonjour,

utilises la fonction FIND pour chercher directement la bonne cellule dans ta colonne, plutôt que d'analyser cellule par cellule

l'aide en ligne VBA te montrera la façon d'utiliser FIND, ainsi que les arguments possibles (notamment le fait de chercher la contenu exact de la cellule .. XlWhole)

tu peux également utiliser l'enregistreur de macro, en simulant une recherche (Ctrl+F) d'une valeur dans ta colonne. T'aura un début de code qu'il suffira d'adapter

si tu coinces, revient avec le code que tu as testé et expliques le problème


EDIT : un exemple tout simple
les numéros sont en colonne 1
les données sont en colonne B et C

on va chercher la valeur 8, et on va écrire dans la fenêtre d'exécution la valeur de la colonne B et C

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
Sub MaRecherche()
Dim Cell As Range
Dim Valeur As Long
Valeur = 8
 
Set Cell = ThisWorkbook.Worksheets("Feuil1").Columns(1).Find(Valeur, , , xlWhole)
 
If Not Cell Is Nothing Then
    Debug.Print Cell.Offset(0, 1).Value
    Debug.Print Cell.Offset(0, 2).Value
Else
    Debug.Print "Recherche n'a pas abouti avec la valeur : " & Valeur
End If
 
End Sub

[esse2016]

Joe.levrai avant tout merci !

ça marche du tonnerre de feu ce "Find".
j'ai essayé sur 65000 éléments (lignes plutôt) et ça rame pas.

Une grosse épine sortie du pied !

MERCI...

problème résolut

[esse2016]

Je réouvre ce post, car à présent :

le traitement de recherche d'une cellule est fait grâce à la méthode FIND(valeur,,,xwhole)
le traitement de suppression d'une ligne entière est réalisée suivante cette commande

Code :

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

1
2
3
4
 
Dim ligne As Long
ligne = Sheets("Feuil1").Columns(2).Find(article, , , xlWhole).Row
Rows(ligne).EntireRow.Delete Shift:=xlUp

le traitement de remplacement reste à faire

si je supprime une ligne alors toutes mes autres lignes remontent (donné par mon shift:=xlup)

mon problème est comment agencé les articles suivant? car ils ont pourvu de numéros décroissant en colonne B


ma première soluce est de traiter ligne à ligne: mais cela risque d'être long ! et je reviens à mon problème de vitesse d'exécution !
avez-vous une piste sans spécialement m'écrire le code ?

je donne un exemple pour être sur d'être compris



si je supprime la ligne excel 40 soit le numéro 45 de la colonne B
alors par ma suppression toutes les autres dessous remonteront de 1 ligne chacune
donc la ligne excel 40 aura le numéro 46 de la colonne B et ça ne va pas car ça sautera de 44 à 46 en colonne B

Si vous savez comment on traite ce problème ??

[joe.levrai]

Bonjour,

plusieurs méthodes possibles, qui dépendent de l'agencement des données

tu peux très bien gérer par formule cette numérotation, si une corrélation entre ce numéro et la ligne excel existe. Voir dans ce cas la fonction Ligne() à laquelle tu ajoutes une constante numérique pour retomber sur le bon numéro. A chaque suppression d'une ligne, les numéros se mettront à jour automatiquement

sinon, après avoir supprimé tes lignes, si les numéros doivent se suivre, il te suffit de faire une recopie incrémentée des numéros en partant de ta première ligne

y'a des dizaines de façons de s'en sortir, sans même utiliser VBA ou (à défaut) sans utiliser de boucle

[esse2016]

merci encore pour ton aide Joe.Levrai mais je n'y arrive pas ! (à moitié disons)

un spécialiste comme toi me dira que je fais encore du ligne par ligne mais comme je débute en vBa excel je n'ai pas de recul sur toutes les techniques les plus optimums. actuellement voici mon code pour renuméroter mes cellules de -1 chacune (je précise que ma suppression se fait depuis un userform) et
je m'interdis de mettre des formules simples dans la feuil1 de la colonne B du genre $B71 = $B70 + 1 etc...
il n'y a pas spécialement de corrélation entre le numéro inscrit dans chaque cellule de la colonne B parce que à chaque saisis dans le formulaire USF la première ligne soit B9 récupère le plus grand numéro de +1 par rapport à son prédécesseur.

la fonction ligne sois je suis nul et je n'ai rien trouvé à ce sujet en vBa soit elle est utile dans la feuille excel chose qui ne me convient pas.
je préfère passé par du vBa. Bon je préviens c'est codé comme un barbare, mais je débute...

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
Private Sub traitement(ByVal ticket As Long)
Dim i, j, ligne, ligne_init, ligne_fin As Double
Dim buffer_ticket As Long
Dim Cell As Range
j = 1
'trouve la ligne du ticket
Set Cell = Sheets("Feuil1").Columns(2).Find(ticket, , , xlWhole)
ligne = Cell.Row
'efface la ligne et remonte ensemble de 1 ligne
Rows(ligne).EntireRow.Delete Shift:=xlUp
 
'reclasse les items
ligne_init = ligne
ligne_fin = Range("B9").End(xlDown).Row
For i = ligne_init To ligne_fin
Set Cell = Sheets("Feuil1").Columns(2).Find(ticket + j, , , xlWhole)
buffer_ticket = Cells(Cell.Row, 2).Value - 1
Cells(i, 2) = buffer_ticket
j = j + 1
Next i
End Sub

pour traiter environ 4000 lignes, le code met au moins 8s avant de me redonner la main => trop long

Suis désespéré, frustré de ne pas voir l'ensemble des fonctionalités/techniques vba.

[casefayere]

C'est une plage en gros ? Si oui est-ce que cela ne va pas augmenté le ralentissement

non, c'est une variable tableau, renseignes-toi à ce sujet, tout se fait grace à ces tableaux et c'est donc beaucoup plus rapide, d'ailleurs, prends de préférence, ma dernière version

pour voir sur les tableaux http://silkyroad.developpez.com/vba/tableaux/

[casefayere]

mets un point d'arrêt sur la ligne

Code :

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

TbG = .Range("B9", .Range("J" & Rows.Count).End(xlUp)) 'voir ci-dessus

et controles la valeur de x juste au-dessus car mes tests sont positifs, maintenant
500 000 lignes je n'ai pas pu tester, je ne penses pas qu'il y ait une limite à "CountIf"

[esse2016]

Merci pour ton aide Casefeyere.

Je comprends un peu mieux. Grosso modo :
On copie une plage de cellule dans un tableau TbG
Puis on créée 2 tableaux à 1D (ou 1seul à 2D) qui sont (est) l'image des colonnes b et j de la feuille
Puis on vient piocher dans tbG pour alimenter tb1 et tb2
Puis remplit la listebox par tb1 et tb2

Ok

Quant à la 2ème version j'essayerai demain au boulot.

Par compte je vois que tu fais TbG =.range("B9",.range("J.........,)
C'est une plage en gros ? Si oui est-ce que cela ne va pas augmenté le ralentissement car finalement entre les colonnes B et J, je n'ai pas besoin d'afficher leurs valeurs dans mon listbox. Dit autrement crois-tu qu'en remplissant tbG uniquement par colonne b et j ça apporterai un gain en vitesse d'execution ? Si oui perso, du moins pour ce soir, je ne vois pas comment on fait... Il doit y avoir des aides certinement sur mdsn.

J'aurai bien aimé que joe me montre une version, mais je ne peux pas vous embeter non plus avec mes problèmes...

En tout cas merci et demain soir je vous tiens au courant

[casefayere]

Code :

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

Set Dcel = .Range("B" & .Rows.Count).End(xlUp)

variable "Range" qui représente la dernière cellule renseignée en B donc, admettons que ce soit B15(au hasard) en conséquence ".Range("B7", Dcel(1, 13))" ou ".Range("B7", Dcel.Offset(0, 12))" représente Range("B7:N15")

[esse2016]

Ah bon, je m'attendais à B7:N15.

Bon ok je vois tout ça demain à tête reposée

[RyuAutodidacte]

Bonsoir,
je suis sur Mac aussi, et tu auras de meilleurs résultat avec les tableaux.
Après selon les tâches à accomplir, cela prendra plus ou moins de temps;
Donc avant tout c'est comprendre ce qui est chronophage dans ces tâches afin de choisir la meilleurs méthode pour que la macro soit plus rapide

Un exemple : sur un de mes posts , assez long, mais dont le cheminement est formateur (Merci Marc ) , on découvre petit à petit ce qui est chronophage pour la macro (en l'occurence, dans mon post, c'est la suppression des lignes (si je me souviens bien)):
Aide pour optimisation code doublons spécifique svp
Bonne lecture

[casefayere]

Bonjour,
sans regarder ton fichier, pour remplir ta ListBox, si tu passes par des tableaux, admettons avec l'évènement "Click"" de la Combo

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
Private Sub ComboBox1_Click()
Dim TbG, Tb1(), ligne As Long, x As Long, Tb2(), TbL()
With Sheets("Feuil1")
  TbG = .Range("B9", .Range("J" & Rows.Count).End(xlUp))
  x = 0
  For ligne = 1 To UBound(TbG, 1)
    If TbG(ligne, 9) = ComboBox1.Value Then
      x = x + 1: ReDim Preserve Tb1(1 To x): ReDim Preserve Tb2(1 To x)
      Tb1(x) = TbG(ligne, 7)
      Tb2(x) = TbG(ligne, 1)
    End If
  Next ligne
  ReDim TbL(1 To UBound(Tb1), 1 To 2)
  For ligne = 1 To UBound(TbL, 1)
      TbL(ligne, 1) = Tb1(ligne)
      TbL(ligne, 2) = Tb2(ligne)
  Next ligne
  ListBox1.List = TbL 'Application.Transpose()
End With
 
End Sub

[esse2016]

Merci Casefayere !


1,62 secondes cela prend à balayer 38485 lignes
3,17 secondes pour 50247 lignes
10 secondes pour 100 000 lignes

et à transposer dans un tableau dont j'aurai été incapable de le faire. Donc je suis jaloux de vos prouesses.

Comme Joe.levrai je ne pige pas toute la macro.

pour l'instant mes problèmes de :
- supprimer les lignes et les reclasser en ordre décroissant dans ma base de donnée -> méthode de joe.levrai => marche super et rapidement bien même avec 100 000 lignes
- faire apparaitre le listing de ma base de donnée dans ma listbox -> méthode de casefayere => je prend bien sur sans froisser quiconque si il y a plus rapide : je demande à voir/tester
- Ma recherche via un textbox prend 3 à 4 secondes par appuye pour corréler le numéro saisit au numéro dans la base dont il fauta afficher les infos des colonnes B F H J L N et plus peut-être

si y a encore plus rapide bien entendu je suis preneur (si joe sais faire avec l'histoire du filtre a transposer dans une autre feuille ...) ou en demanderai-je trop ...

en tout cas merci, mes problèmes sont presque tous résolus

Petite précision pour casefayere.

Je l'ai mis dans combobox_change()
Car ma première value du combo est "".

Donc si je le mets dans l'evenement combobo_click comment le code peut savoir quel listing à afficher ?

[casefayere]

- différence entre pc et mac

pourquoi, tu es sur Mac ?, tu ne l'a pas signalé dans ton titre

De plus je ne comprends pas le dcel(1,13) ligne 1 colonne13 ?

on va chercher la dernière cellule rnseignée en B donc " dcel(1,13)" représente la colonne N, c'est comme si j'écrivais

Code :

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

1
2
.Range("B7", Dcel.Offset(0, 12)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "P1:R2"), CopyToRange:=.Range("P1:Q1"), Unique:=False

[esse2016]

Oui chez moi sous mac et au boulot sous pc.

La je suis un peu crevé, mais au taff avec ta méthode de 1tableau à 2D j'obtenais 2s pour 100000 lignes de mémoire hélas je n'ai pas comparé sur mac


Deplus
Je crois que c'est range("B7",.offset(0,12)) qui me pertube
Msdn microsft dit que range(cell1,cell2) grosso modo c'est un rectangle coin référence cell1 à autre coin cell2
Donc une selection quelque part
Et donc
Est ce que ça veut dire qu'on selectionne une plage B7:N7 ?

Et je ne pige pas pourquoi il faut commencer en B7 alors que mes données commencent en b9

[casefayere]

Petite précision pour casefayere.

Je l'ai mis dans combobox_change()

je ne donne qu'un exemple, après peu m'importe l'évènement
Dès que j'ai 5 mn, je te renvoie le code avec explications

il y a surement mieux mais, comme promis

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
23
24
25
26
Private Sub ComboBox1_Click() 'donc à toi de voir l'évènement
ci 'dessous, je déclare _
 Tbg qui comprendra toutes les données de B à J _
 Tb1() qui reprendra les données de H _
 Tb2() qui reprendra les dnnées de B _
 TbL() qui reprendra Tb2 en 1ère dimension, Tb1 en 2ème dimension
Dim TbG, Tb1(), Tb2(), TbL(), ligne As Long, x As Long
With Sheets("Feuil1")
  TbG = .Range("B9", .Range("J" & Rows.Count).End(xlUp)) 'voir ci-dessus
  x = 0
  For ligne = 1 To UBound(TbG, 1) 'on boucle sur le variable TbG
    If TbG(ligne, 9) = ComboBox1.Value Then 'la condition (traduit si J&ligne = combo)
      x = x + 1: ReDim Preserve Tb1(1 To x): ReDim Preserve Tb2(1 To x) 'je redimensionne les deux autres tableaux à 1 dimension
      Tb1(x) = TbG(ligne, 7) 'les valeurs de H&ligne pour Tb1(x)
      Tb2(x) = TbG(ligne, 1) 'les valeurs de B&ligne pour Tb2(x)
    End If
  Next ligne
  ReDim TbL(1 To UBound(Tb1), 1 To 2) 'je dimensionne à 2 dimensions, 1ère dimension le nombre de lignes, 2ème dimesion 2 colonnes (B & H)
  For ligne = 1 To UBound(TbL, 1) 'je boucle sur un des deux tableaux
      TbL(ligne, 1) = Tb1(ligne) '1ère dimension egal à B triée
      TbL(ligne, 2) = Tb2(ligne) '2ère dimension egal à H triée
  Next ligne
  ListBox1.List = TbL 'le résultat dans la ListBox
End With
 
End Sub

à tester si plus rapide 2 tableaux en moins

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
Private Sub ComboBox1_Click() 'donc à toi de voir l'évènement
'ci-dessous, je déclare _
 Tbg qui comprendra toutes les données de B à J _
 Tb1() qui reprendra les données de B et H
Dim TbG, Tb1(), ligne As Long, x As Long
With Sheets("Feuil1")
  x = WorksheetFunction.CountIf(.Range("B9", .Range("J" & Rows.Count).End(xlUp)), ComboBox1.Value)
  TbG = .Range("B9", .Range("J" & Rows.Count).End(xlUp)) 'voir ci-dessus
  ReDim Tb1(1 To x, 1 To 2)
  x = 1
  For ligne = 1 To UBound(TbG, 1) 'on boucle sur le variable TbG
    If TbG(ligne, 9) = ComboBox1.Value Then 'la condition (traduit si J&ligne = combo)
      Tb1(x, 1) = TbG(ligne, 1): Tb1(x, 2) = TbG(ligne, 7): x = x + 1
    End If
  Next ligne
  ListBox1.List = Tb1 'le résultat dans la ListBox
End With
 
End Sub