Microsoft 365 VBA Etendre formule jusqu'à la dernière ligne dans VBA

[AD95]

Bonjour,

La formule que j'ai s'arrête à la la colonne Y
Je souhaite étendre la formule suivante à partir de la colonne L9 jusqu'à la dernière colonne et exécuter la formule jusqu'à la dernière ligne de la colonne A

version excel : =SI(NB.SI($L9:$Y9;"KO")>0;"Y";"-")


version VBA : pl.Formula = "=IF(COUNTIF($L9:$Y9,""OK"")>0,""Y"",""-"")"


D'avance, merci de votre expertise

 

[sylvanu]

Bonjour AD,
Un petit fichier test aurait été le bienvenu. Par ex pour comprendre pourquoi en formule on cherche les KO et en VBA les OK ?
Alors un peu en aveugle :

Sub OKprésent()
DL = [L65500].End(xlUp).Row
Formule = "=IF(COUNTIF($L9:$Y9,""OK"")>0,""Y"",""-"")"
Range("A9:A" & DL).Formula = Formule
End Sub

 

[AD95]

Bonjour AD,
Un petit fichier test aurait été le bienvenu. Par ex pour comprendre pourquoi en formule on cherche les KO et en VBA les OK ?
Alors un peu en aveugle :

Sub OKprésent()
DL = [L65500].End(xlUp).Row
Formule = "=IF(COUNTIF($L9:$Y9,""OK"")>0,""Y"",""-"")"
Range("A9:A" & DL).Formula = Formule
End Sub

Bonjour Sylvanu,

Merci pour ton retour.

Dsl, Je viens de mettre un exemple en PJ. (Pour info je ne pas ouvrir les PJ je suis bloqué par le travail)

 

[sylvanu]

Dsl, Je viens de mettre un exemple en PJ.

Pas de PJ dans votre demande. Sorry.

Pour info je ne pas ouvrir les PJ je suis bloqué par le travail

Alors testez ma macro sur votre fichier.

 

[AD95]

Pas de PJ dans votre demande. Sorry.

Alors testez ma macro sur votre fichier.

Ca marche pas

 

[sylvanu]

Ca marche pas

Sans fichier test, impossible de vous aider.
On ne peut que supputer :
DL = [L65500].End(xlUp).Row : On regarde où se trouve la dernière ligne de la colonne L
Formule = "=IF(COUNTIF($L9:$Y9,""OK"")>0,""Y"",""-"")" : Formule utilisée
Range("A9:A" & DL).Formula = Formule : On colle la formule de A9 jusqu'à la dernière ligne de la colonne A.

Je souhaite étendre la formule suivante à partir de la colonne L9 jusqu'à la dernière colonne et exécuter la formule jusqu'à la dernière ligne de la colonne A

Sauf que votre demande est ambiguë, car L9 n'est pas une colonne, et on ne peut pas coller en L9 une formule qui contiendrait L9, on aurait un problème de référence circulaire.
Donc précisez, ou fournissez un fichier test pour comprendre ce que vous voulez faire.

 

[AD95]

Bonjour Sylvanu,

J'ai mis le fichier en PJ. Le début des données commence par la colonne L et la ligne 9.
Le problème c'est que les données sont aléatoires la dernière colonne et la dernière ligne change continuellement. C'est la raison pour laquelle j'ai besoin d'étendre la formule jusqu'à la dernière colonne et la dernière ligne.

 

[sylvanu]

Bonjour AD,
Attention, dans votre fichier la formule est =SI(NB.SI($L9:$Y9;"K0")>0;"Y";"-") au lieu de =SI(NB.SI($L9:$Y9;"KO")>0;"Y";"-"). Ce qui donne des résultats totalement différents.
Un essai en PJ avec :

=SI(NB.SI($L9:$ZZ9;"KO")>0;"Y";"-")

J'ai considéré qu'à droite du tableau il n'y avait rien.
A noter que si vous passez votre tableau en tableau structuré, le VBA n'est pas utile. Voir Feuil2.
La formule se propagera jusqu'à la fin du tableau automatiquement.

 

[AD95]

Bonjour AD,
Attention, dans votre fichier la formule est =SI(NB.SI($L9:$Y9;"K0")>0;"Y";"-") au lieu de =SI(NB.SI($L9:$Y9;"KO")>0;"Y";"-"). Ce qui donne des résultats totalement différents.
Un essai en PJ avec :

=SI(NB.SI($L9:$ZZ9;"KO")>0;"Y";"-")

J'ai considéré qu'à droite du tableau il n'y avait rien.
A noter que si vous passez votre tableau en tableau structuré, le VBA n'est pas utile. Voir Feuil2.
La formule se propagera jusqu'à la fin du tableau automatiquement.

Bonjour Sylvanu,

Merci pour ton retour. Est-il possible pour toi de me l'envoyer ici en format texte ? Je ne peux pas ouvrir de PJ je suis verrouillé par le travail. D'avance, merci

 

[AD95]

Bonjour Sylvanu,

Merci pour ton retour. Est-il possible pour toi de me l'envoyer ici en format texte ? Je ne peux pas ouvrir de PJ je suis verrouillé par le travail. D'avance, merci

 

[sylvanu]

de me l'envoyer ici en format texte

Le nombre de lignes est calculé par DL, le nombre de colonnes est arbitrairement fixé de L à ZZ.

Sub OKprésent()
DL = [L65500].End(xlUp).Row
Formule = "=IF(COUNTIF($L9:$ZZ9,""KO"")>0,""Y"",""-"")"
Range("K9:K" & DL).Formula = Formule
End Sub

 

[AD95]

ça marche parfaitement bien (merci beaucoup sylvanu) mais ça répond à moitié au besoin
pour la dernière ligne c'est parfait (variable dl) ça répond au besoin
mais pour la dernière colonne j'aurai besoin de faire pareil (avec variable dc par exemple) sans mettre ZZ (il doit pouvoir retrouver la dernière colonne et s'arrêter à la dernière colonne)

J'ai un exemple de formule qui fonctionne mais j'arrive pas à l'adapter avec ma formule.
Comme variable j'ai dl (pour dernière ligne et dc pour dernière colonne)

Exemple :
Dim dl%, dc%,
Dim plage As Range, pl As Range

.Range(.Cells(3, 12), .Cells(3, 11 + dc)).Formula = "=COUNTIF(L$9:L$" & dl & ",""OK"")"

 

[sylvanu]

Re,
Votre exemple ne peut pas marcher.
1- Il faut calculer "dc". Or pour calculer "dc" il faut savoir où s'arrêter sinon vous déborderez de votre tableau.
2- L$9:L$" & dl : dl vaut 14 donc vous obtiendrez L$9:L$14 donc le bon nombre de lignes mais pas le bon nombre de colonnes.
3- .Range(.Cells(3, 12), .... vous collez la formule dans la colonne C et non la colonne K.
Question : Avez vous après votre tableau à droite des cellules qui contiendraient OK ou KO ? Car sinon vous pouvez analyser jusqu'à ZZ, les cellules vides ne seront pas pris en compte.
Sinon le plus simple comme déjà dit est de transformer votre tableau en Tableau structuré. Avec comme formule :

=SI(NB.SI(Tableau1[[#Cette ligne];[test 1]:[test 16]];"KO")>0;"Y";"-")

Ainsi la formule s'adapte automatiquement au bon nombre de lignes et colonnes, et se propage automatiquement si on rajoute des lignes.

 

[AD95]

Re,
Votre exemple ne peut pas marcher.
1- Il faut calculer "dc". Or pour calculer "dc" il faut savoir où s'arrêter sinon vous déborderez de votre tableau.
2- L$9:L$" & dl : dl vaut 14 donc vous obtiendrez L$9:L$14 donc le bon nombre de lignes mais pas le bon nombre de colonnes.
3- .Range(.Cells(3, 12), .... vous collez la formule dans la colonne C et non la colonne K.
Question : Avez vous après votre tableau à droite des cellules qui contiendraient OK ou KO ? Car sinon vous pouvez analyser jusqu'à ZZ, les cellules vides ne seront pas pris en compte.
Sinon le plus simple comme déjà dit est de transformer votre tableau en Tableau structuré. Avec comme formule :

=SI(NB.SI(Tableau1[[#Cette ligne];[test 1]:[test 16]];"KO")>0;"Y";"-")

Ainsi la formule s'adapte automatiquement au bon nombre de lignes et colonnes, et se propage automatiquement si on rajoute des lignes.

Bonjour Sylvanu,

Malheureusement je ne sais jamais quand la donnée va s'arrêter c'est aléatoire. Le problème tu tableau structuré c'est que j'ai d'autre formule qui se lance sur ces données je peux pas trop y toucher.

Je vais rester sur ton bout de code pour le moment le temps de trouver sur d'autres sites (si quelqu'un à eu le même problème que moi) une autre solution si possible .

Merci pour tout et bonne journée.

Sub OKprésent()
DL = [L65500].End(xlUp).Row
Formule = "=IF(COUNTIF($L9:$ZZ9,""KO"")>0,""Y"",""-"")"
Range("K9:K" & DL).Formula = Formule
End Sub