Detection cel. vide sous formule

[herve62]

Bonjour
Il y déjà assez longtemps , j'avais été confronté à trouver une dernière ligne vide dans une plage avec des formules
A force de chercher j'avais trouvé ( Getderligne)
J'ai à nouveau le cas , donc je dis Bingo , je connais ... et Ben non CA MARCHE pas ??
J'ai retrouvé un sujet d'ici de 2005 ( bien fait) je l'ai ajouté pour tester les options et aucune ne me donne le bon Résultat
Y a t-il une autre méthode ??
J'ai aussi testé ( de Jean Pierre) :

If Sheets("Feuil1").Cells(k, 1) = vbNullString Then
   
    Derlig = k + 1

MERCI

 

[Dranreb]

Re : Detection cel. vide sous formule

Bonjour.

Moi je propose souvent, avec d'autres fournitures qui les utilisent, ces fonctions qui renvoient la plage désirée à partir d'une cellule de début spécifiée.

Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing) As Range
Rem. Cherche la partie utilisée d'une plage, c'est à dire celle qui s'étend jusqu'à la dernière cellule renseignée de plus qu'une chaîne vide.
'    PlageDép: Plage de départ. Seule la 1ère cellule spécifiée est prise en compte pour la déterminer, et suffit donc pour une utilisation VBA.
'            Pour utilisation en formules, il est néanmoins nécessaire de spécifier la plage depuis celle ci jusqu'au reste des colonnes entières.
'    PlageExam: Plus grande plage susceptible de contenir la plage cherchée.
'            Facultatif: UsedRange assumé par défaut.
Dim LMax As Long, CMax As Long, NbL As Long, NbC As Long
On Error GoTo RienTrouvé
If PlagExam Is Nothing Then Set PlagExam = PlageDép.Worksheet.UsedRange
LMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
CMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column
On Error GoTo 0
NbL = LMax - PlageDép.Row + 1: If NbL < 1 Then GoTo CEstToutVide
NbC = CMax - PlageDép.Column + 1: If NbC < 1 Then GoTo CEstToutVide
Set PlgUti = PlageDép.Resize(NbL, NbC)
Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
End Function
'

Function ColUti(ByVal PlageDép As Range) As Range
Rem. Comme PlgUti, mais la plage rendue se limitera aux colonnes couvertes par la plage de départ spécifiée
Set ColUti = PlgUti(PlageDép, Intersect(PlageDép.Worksheet.UsedRange, PlageDép.EntireColumn))
End Function

 

[herve62]

Re : Detection cel. vide sous formule

Re
Yups !! fichier pas passé , le voici
Dranreb :

Moi je propose souvent, avec d'autres fournitures qui les utilisent, ces fonctions qui renvoient la plage désirée à partir d'une cellule de début spécifiée

Ou ca aussi ????? :

Function PlageÀPartirDe(ByVal PlageDép As Range) As Range
Dim F As Worksheet, LDéb As Long, CDéb As Long, LMax As Long, CMax As Long, Plg As Range
Set F = PlageDép.Worksheet: LDéb = PlageDép.Row: CDéb = PlageDép.Column
With F.UsedRange: LMax = .Row + .Rows.Count - 1: CMax = .Column + .Columns.Count - 1: End With
Do:
  If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(LMax - LDéb + 1), "") < LMax - LDéb + 1 Then Exit Do
   LMax = LMax - 1: Loop Until LMax < LDéb
Do:
  If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(, CMax - CDéb + 1), "") < CMax - CDéb + 1 Then Exit Do
   CMax = CMax - 1: Loop Until CMax < CDéb
If LMax < LDéb Or CMax < CDéb Then Set PlageÀPartirDe = Nothing: Exit Function
Set PlageÀPartirDe = PlageDép.Resize(LMax - LDéb + 1, CMax - CDéb + 1)
End Function

qui ne fonctionne pas

 

[Dranreb]

Re : Detection cel. vide sous formule

Acec ma fonction PlgUti, cette procédure affiche 3:

Sub Test()
With PlgUti([A2])
   MsgBox .Rows.Count + .Row - 1
   End With
End Sub

 

[herve62]

Re : Detection cel. vide sous formule

je veux bien mais j'aimerai bien avoir Mon fichier en retour et voir comment ça fonctionne
Car là , j'ai un gros DOUTE ???? ( Msgbox = 15 ... et pas 3)!!

 

[Dranreb]

Re : Detection cel. vide sous formule

C'est parce que les formules renvoient un espace, ce qui est quand même plus conséquent qu'une chaine vide. Ça affiche bien 3 en mettant par exemple comme formule jusqu'à la ligne 15 :

=SUPPRESPACE(B2&" "&C2)

Avec cette formule aussi :

=B2&SI(ET(B2<>"";C2<>"");"   ";"")&C2

 

[herve62]

Re : Detection cel. vide sous formule

Ok , dans l'exemple cela fonctionne
Donc passons aux choses sérieuses, le coeur du Pb
Je joins mon Fichier de la réelle Appli
et STP , explique moi ce qui va pas ?? (msgbox retourne 121 ????)

code : WITH dans l'USF PERMIS .... puis ComboxBox
la function dans Module 1

MDP : 0000
Merci !

 

[Dranreb]

Re : Detection cel. vide sous formule

Il y a bien une dernière cellule L21 non vide dans la feuille LUTIN, qui doit être la feuille active à ce moment là je suppose ?
Il serait plus prudent de toujours spécifier, suivi d'un point, devant tout [, Cells, Range, le nom de l'objet Worksheet concerné tel qu'il figure dans la rubrique Microsoft Excel Objets, devant le nom de la feuille Excel qu'il représente, rappelé entre parenthèses.

Si seule la colonne A doit être examinée vous pouvez prendre plutôt ColUti(Feuil1.[A12])

 

[herve62]

Re : Detection cel. vide sous formule

L21 ????
Sinon comme on dit ici : FICHIER JOINT = Pb pratiquement RESOLU
Donc mon fichier est LA !!
Donc juste le tester et me dire Pourquoi ça marche pas , et que faire ?

Joindre un classeur, support potentiel de la solution à votre problème concernant des modèles de données et de résultats souhaités y figurant, n'est pas une obligation: Ça ne fait qu'entrouvrir la possibilité d'obtenir une réponse de ma part !
(voire de la vôtre: un problème bien posé étant à moitié résolu)

Je pense être tombé dans l'autre MOITIE 🙂 !!!!!!
Je joins la nouvelle version modifiée comme t'as dis en utilisant COLUTI
As tu testé ??

 

[Dranreb]

Re : Detection cel. vide sous formule

Excuses: le 1er 1 de 121 avait sauté. Il y a une coche "ü" en police Wingdings en L121.
Donc PlgUti(Feuil1.[A12]) c'est LUTIN!A12:AA121.
Mais ColUti(Feuil1.[A12]) c'est LUTIN!A12.
…qu'on pourrait utiliser par exemple dans l'Userform_Initialize :

With ColUti(Feuil1.[A12])
   If .Rows.Count = 1 Then ComboBox1.List = Array(.Value) Else ComboBox1.List = .Value
   End With

Remarque: perso je renommerais l'objet Workheet Feuil1 qui représente la feuille Excel "LUTIN" en FLutin

 

[herve62]

Re : Detection cel. vide sous formule

Bon j'aimerai bien en finir !
c'est Quoi la syntaxe pour que cela fonctionne ( car toujours ERREUR , même en Modifiant comme ci-dessous)
c'est pas compliqué de lancer mon appli et de voir ce qu'elle fait et ..... ET Pour Ceux qui savent,
Réparer !

With ColUti(LUTIN!A12)
   MsgBox .Rows.Count + .Row - 1
   l = .Rows.Count + .Row - 1
   End With

Merci !!

 

[Dranreb]

Re : Detection cel. vide sous formule

Vous ne suivez pas mes indications.
Le nom de l'objet VBA de type Worksheet qui représente la feuille Excel qui, dans sa collection à lui, est connue sous le nom de "LUTIN" c'est actuellement Feuil1 et non pas LUTIN.
Fiez vous à la rubrique Microsoft Excel Objets du projet VBA. Mais vous pouvez le renommer en LUTIN si vous voulez. C'est la toute première propriété (Name) dans la fenêtre de propriétés.

 

[herve62]

Re : Detection cel. vide sous formule

Bon , Ben si c'est si simple fait LE !!! que je comprenne ?
là je vois pas ?
je donne ma langue au chat !!

 

[Dranreb]

Re : Detection cel. vide sous formule

Utilisez PlgUti(Feuil1.[A12]) si vous voulez la plage contenant toutes les cellule de "LUTIN" à partir de la ligne 12, renseignées de plus qu'une chaine vide, et ColUti(Feuil1.[A12]) si vous en voulez seulement celles de la colonne A, c'est tout.

 

[herve62]

Re : Detection cel. vide sous formule

ENFIN !!
j'ai fait

With ColUti(Feuil1.[A12])
   MsgBox .Rows.Count + .Row - 1
   l = .Rows.Count + .Row
   End With

sans trop bien comprendre ( hélas ingénieur has been !!) , mais bon !
dans les objets ma feuille s'appelle LUTIN ?
Merci bien ! au moins ça marche ( supprimer le -1 afin de recopier à la ligne suivante !!)