Balayage d'une fonction sur une plage de cellules, par macro vba

[white-spirit]

Bonsoir,

comme indiqué par ailleurs, je reviens ici pour présenter une nouvelle problématique d' analyse sur une colonne de données.
Sur le fichier joint, une présentation permet de comprendre ma recherche.

Cette recherche consiste à analyser une colonne de données (A4 à Ax) au momentde la saisie d'une valeur en Ax
2 critères sont recherchés:
- la nième aparition d'une valeur N dans la colonne A (résultat en Cx)
- le nb total d'apparition de cette valeur N dans la colonne A (résultat en Dx)

x représentant la ligne à laquelle la saisie se fait

J'ai besoin du balayage de la conne complète (A4:A30) pour les raions suivantes:
- possibilité externe d'insérer une ligne nouvelle au milieu des saisies présentes en A, et entrée d'une nouvelle valeur N dans la cellule vide
- possibilité d'effacer une saisie en A, sans supprimer la ligne
- possibilité de remplacer l'une desvaleurs saisie en Ax

Un calcul sur plage progressive (selon la ligne de saisie) fonctionne avec des formules en automatic, mais pas avec une macro sur la ligne only.
Il faut surement utiliser une boucle, mais jene sais pas encore le faire correctement ...

Nota: sur 30 lignes, le matriciel convient, mais cen'est qu'un extrait, je l'étendrai à 4000 plus tard. Et là, le temps de calcul des formules est trop long !!

Si qq'un(ou une!) a une solution à me proposer, j'en serais ravi !

Merci d'avance.

Cdlt

WS.

 

[ROGER2327]

Re : Balayage d'une fonction sur une plage de cellules, par macro vba

Bonjour white-spirit

Ceci, peut-être ?

[COLOR="DarkSlateGray"][B]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range, oDat()
  If Not Intersect(Target, Columns(1)) Is Nothing Then
    With Range(Cells(1, 1), Cells(WorksheetFunction.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 3).End(xlUp).Row) - 2, 4)).Offset(3)
    oDat = .Value
    With .Resize(, 1)
      For Each oCel In .Cells
        oDat(oCel.Row - 3, 3) = WorksheetFunction.CountIf(Range(Cells(1, 1), oCel), oCel.Value)
        oDat(oCel.Row - 3, 4) = WorksheetFunction.CountIf(.Cells, oCel.Value)
      Next
    End With
    Application.EnableEvents = False
    .Value = oDat
    Application.EnableEvents = True
    End With
  End If
End Sub[/B][/COLOR]

Mais je n'ai pas vérifié que ce soit plus rapide que des formules.​

ROGER2327
#4262

Samedi 28 Absolu 138 (Xylostomie, V)
14 Vendémiaire An CCXIX
2010-W40-2T01:57:27Z

 

[white-spirit]

Re : Balayage d'une fonction sur une plage de cellules, par macro vba

Bonjour Roger,
Bonjour le Forum,

que dire, Roger...., à part MERCI !! puisque le code proposé fonctionne parfaitement dans l'exemple, et aussi dans mon besoin (après adaptation du déclenchement).

Et je peux te dire que le calcul est sacrément + rapide que les formules, surtout après avoir adapté le code pour 4000 lignes de saisie !
Je n'arrive d'ailleurs pas à saisir assez vite pour le buguer !!

Il existe une étape supplémentaire que j'ai décris dans le nouveau fichier joint.
Il s'agit ici aussi de remplacer 8000 formules (2 col x 4000 lg) pour extraire un "ordre" temps réel des valeurs saisies (en nombre et en antériorité).
Le déclenchement est lié à la même saisie que l'appli précédente.
(l'utilisation est l'affichage d'un classement live de dossards de cyclistes saisis au fil des passsages au pointage)
Ce qui apparait en rouge dans la colonne position est exploité pour afficher des noms dans un tableau d'affichage public.

Si tu est motivé pour regarder le code.... je t'en remercie vivement ( ou tout autre qui s'y intéresse, bien sûr !)

Bien cordialement

WS.

 

[ROGER2327]

Re : Balayage d'une fonction sur une plage de cellules, par macro vba

Re...

Il serait tellement plus simple de poser complètement le problème d'entrée de jeu...

Voici un bricolage sur l'existant, mais il doit y avoir mieux à faire en reprenant l'ensemble du problème :

[COLOR="DarkSlateGray"][B]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range, oDat(), x
  If Not Intersect(Target, Columns(1)) Is Nothing Then
    With Range(Cells(1, 1), Cells(WorksheetFunction.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 3).End(xlUp).Row, Cells(Rows.Count, 6).End(xlUp).Row, Cells(Rows.Count, 7).End(xlUp).Row) - 2, 7)).Offset(3)
    oDat = .Value
    With .Resize(, 1)
      For Each oCel In .Cells
        oDat(oCel.Row - 3, 3) = WorksheetFunction.CountIf(Range(Cells(1, 1), oCel), oCel.Value)
        oDat(oCel.Row - 3, 4) = WorksheetFunction.CountIf(.Cells, oCel.Value)
        If oDat(oCel.Row - 3, 4) = 0 Then oDat(oCel.Row - 3, 3) = Empty: oDat(oCel.Row - 3, 4) = Empty
        If Not IsEmpty(oCel) Then
          If oDat(oCel.Row - 3, 3) = oDat(oCel.Row - 3, 4) Then oDat(oCel.Row - 3, 7) = 100 * oDat(oCel.Row - 3, 3) - oCel.Row Else oDat(oCel.Row - 3, 7) = 0
        Else
          oDat(oCel.Row - 3, 7) = ""
        End If
      Next
    End With
    Application.EnableEvents = False
    .Value = oDat
    Application.EnableEvents = True
    oDat = .Value
    With .Resize(, 1)
      For Each oCel In .Cells
        If Not IsEmpty(oCel) And oCel.Offset(0, 6).Value <> 0 Then
          x = WorksheetFunction.Rank(oCel.Offset(0, 6).Value, .Offset(0, 6), 0)
          If x < 11 Then oDat(oCel.Row - 3, 6) = x Else oDat(oCel.Row - 3, 6) = ""
        Else
          oDat(oCel.Row - 3, 6) = ""
        End If
      Next
    End With
    Application.EnableEvents = False
    .Value = oDat
    Application.EnableEvents = True
    End With
  End If
End Sub[/B][/COLOR]

(C'est vraiment du replâtrage ! Pas de quoi être fier...)​

ROGER2327
#4266

Samedi 28 Absolu 138 (Xylostomie, V)
14 Vendémiaire An CCXIX
2010-W40-2T23:55:07Z

 

[white-spirit]

Re : Balayage d'une fonction sur une plage de cellules, par macro vba

Bonsoir Roger 2327,
Bonsoir tout le monde,


Désolé Roger de n'avoir pû délivrer en une fois la problématique totale...
Je découvrais en même temps de mon côté le profil à appliquer à mon classeur.

Cependant, vous avez effectué un excellent travail ! que j'ai un peu peiné à adapter à mon besoin( colonnes différentes), mais j'y suis arrivé en me forçant à analyser le code...
Et l'application fonctionne EXACTEMENT comme je le voulais ! .... à 1 détail près : (c'est le dernier !)

- pour mon cas, les colonnes où s'inscrivent les résultats ne sont pas F & G comme dans l'exemple proposé, mais EJ & EK, donc + loin à droite !

Il se trouve que j'ai un tableau de formules présent de F9 à M18, qui se trouve perturbé (par le code que nous venons d'installer), de la façon suivante:

je saisis ligne après ligne des valeurs numériques (dossards) à partir de la ligne 4,
dès que j'atteinds la ligne 8, les formules de la ligne 9 s'effacent dans le tableau, en n'affichant que le résultat du dernier calcul effectué !

Et ainsi de suite pour tout le tableau, qui devient une matrice de constantes !!

J'ai découvert aussi qu'à chaque exécution du code il fallait déprotéger-reprotéger les cellules verrouillées (tableau F9:M18), sinon vba se plante.

Je crois comprendre que les déclarations évoquent une plage complète (jusqu'à colonne EK), c'est peut-être pour cela que les formules à l'intérieur sont "mangées" ?

Qu'en pensez-vous ?

Si pas d'issue, je serais obligé de remplacer le tableau par du code, effectué à chaque saisie. ( essentiellement de la rechercheV).

Si qq'un connait ce problème....

Merci d'avance

Cordialement.

WS.

 

[ROGER2327]

Re : Balayage d'une fonction sur une plage de cellules, par macro vba

Re...

En clair, le problème à résoudre n'a rien à voir à celui que vous exposez dans votre classeur. Je n'y ai en effet trouvé ni plage protégée, ni formule en F9:M18 et pas plus de formule dans la zone de saisie des passages (colonne A). Par définition, il ne peut d'ailleurs pas y en avoir si c'est une zone de saisie. (Une saisie dans une cellule n'est pas compatible avec la présence d'une formule dans la dite cellule.) Bref, déposez un classeur représentant véritablement votre problème, avec toutes ses contraintes.
En attendant, je joins un classeur sans code, mais avec quelques formules et plages nommées qui permet d'établir le classement des dix premiers.

Mode d'emploi :

1. Établir la liste des engagés (le nom est facultatif, le n° de dossard est obligatoire).
2. Saisissez les n° de dossard au fur et à mesure de leur passage dans la colonne A : le classement se fera en temps réel.

En dehors de la colonne A (qui ne doit contenir que les n° saisis) et des zones "Engagés" et "Classement", vous pouvez disposer du reste pour tout calcul nécessaire.​

ROGER2327
#4268

Mercredi 4 Haha 138 (Sainte Berthe de Courrière, égérie, SQ)
18 Vendémiaire An CCXIX
2010-W40-6T10:17:08Z