Autres Row DERLIGNE (si formule ds Col. restitue "" ou Erreur)

[zebanx]

Bonjour à tous,

Je souhaiterai bénéficier de vos lumières concernant la recherche de la valeur d'une dernière ligne dans une colonne qui présente pour particularité d'utiliser des formules et dont les dernières lignes présentent soit :
- une valeur d'erreur (#valeur...)
- une valeur "" en cas d'erreur

En premier lieu, des lignes comme
derligne = cells(rows.count,3).end(3).row
derligne = Range("e1", Range("e" & Rows.Count).End(xlUp)).Count
traiteraient logiquement les dernières lignes du tableau comme non vides.

Un fichier présente l'avancement formules / UDF mais je bute sur l'une ou l'autre des configurations.

Auriez-vous svp, par l'intermédiaire d'une ligne directe ou d'un UDF, une solution au traitement de ces deux configurations ?
(Ou l'approche souhaitable consisterait-elle systématiquement à dégager les formules qui afficheraient soit Erreur/"" en partant du bas de colonne ?)

Vous remerciant par avance pour vos avis/conseils, bonne journée
zebanx

 

[mapomme]

Bonjour @zebanx

Un essai via UDF avec deux fonctions. L'une renvoie le numéro absolu de ligne (par rapport à la ligne 1 de la feuille), l'autre renvoie le numéro relatif de ligne (par rapport à la ligne 1 du range en paramètre).

Function DerLigRel(xrg As Range)
Dim der$, n1, n2
  On Error Resume Next
  n1 = Application.Match(1E+99, xrg(1, 1).EntireColumn): If IsError(n1) Then n1 = xrg.Row
  n2 = Application.Match(String(99, "z"), xrg(1, 1).EntireColumn): If IsError(n2) Then n2 = xrg.Row
  If n2 > n1 Then n1 = n2
  Do While IsError(xrg(n1 - xrg.Row + 1, 1)) Or xrg(n1 - xrg.Row + 1, 1) = ""
    n1 = n1 - 1
  Loop
  DerLigRel = n1 - xrg.Row + 1
End Function

Function DerLigAbs(xrg As Range)
Dim der$, n1, n2
  On Error Resume Next
  n1 = Application.Match(1E+99, xrg(1, 1).EntireColumn): If IsError(n1) Then n1 = xrg.Row
  n2 = Application.Match(String(99, "z"), xrg(1, 1).EntireColumn): If IsError(n2) Then n2 = xrg.Row
  If n2 > n1 Then n1 = n2
  Do While IsError(xrg(n1 - xrg.Row + 1, 1)) Or xrg(n1 - xrg.Row + 1, 1) = ""
    n1 = n1 - 1
  Loop
  DerLigAbs = n1
End Function

 

[Dranreb]

Bonjour
On peut peut être cerner le Range par Set Rng = Columns("J").SpecialCells(xlCellTypeFormulas, 3)
C'est rare que je calcule la dernière ligne parce que je ne parcours jamais les cellules, je charge toujours la valeur de toute la plage qui m'intéresse dans un tableau, et je travaille ensuite à partir de celui ci, c'est bien plus rapide.

 

[zebanx]

Bonjour à tous les deux

@mapomme
Je te remercie beaucoup pour ces formidables fonctions !
Là, pour le coup, ça réclamait du "gros" code, mais il était important d'avoir une expertise de ce calibre.
Je vais l'utiliser rapidement dans mon code (pour info : j'ai du modifier "as range" par "as variant" pour l'utiliser indirectement dans la procédure -> normal!)

Et je vais regarder dans la journée pour l'adapter à une recherche sur la dernière colonne. S'il y a un problème, je me permettrais de revenir vers toi

@Dranreb
C'est très juste.
Cependant, et c'est une erreur d'interprétation de ma part par rapport à la dernière phrase de ton commentaire, lorsque par exemple je définis un tableau comme habituellement par
ta = ActiveSheet.Range("F2:F" & Cells(Rows.Count, 6).End(3).Row).Value
avec un debug print ou une restitution du type Cells(15, 20) = UBound(ta, 1)
la valeur communiquée est de 8 au lieu de 5 (ie : logique!)

--> Si tu peux juste STP m'apporter une précision sur la définition de "je charge toujours la valeur de toute la plage qui m'intéresse dans un tableau" y compris parce que plus rapide par un petit exemple très simple, je t'en remercie par avance.

Et pour l'instruction
Set Rng = Columns("J").SpecialCells(xlCellTypeFormulas, 3)
en cherchant à afficher l'adresse de cette plage, elle contient pour info aussi les valeurs "" (soit en l'espèce "$F$2:$F$9)

En tout cas, un grand merci pour vos précieuses remarques et codes.
zebanx

 

[Dranreb]

À ma grande surprise je n'ai pas pu utiliser la méthode SpecialCells dans une fonction invoquée dans une formule de cellule.
Mais ce n'est pas le but, n'est ce pas. Oui, je sais, SpecialCells(xlCellTypeFormulas, 3) ne repère que les formules non en erreur.
Pour récupérer une plage jusqu'à la dernière cellule renseignée de plus qu'une chaîne vide j'utilise en général ma fonction ColUti :

Function ColUti(ByVal PlageDép As Range, Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
   Set ColUti = PlgUti(PlageDép, Intersect(PlageDép.Worksheet.UsedRange, PlageDép.EntireColumn), LMin, CMin)
   End Function
Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing, _
   Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
   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 < LMin Then NbL = LMin
   NbC = CMax - PlageDép.Column + 1: If NbC < CMin Then NbC = CMin
   If NbL < 1 Or NbC < 1 Then GoTo CEstToutVide
   Set PlgUti = PlageDép.Resize(NbL, NbC)
   Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
   End Function

Alors si ça peut vraiment être indifféremment soit l'un soit l'autre :

TDon = ColUti([J2])).SpecialCells(xlCellTypeFormulas, 3).Value

 

[mapomme]

Re @zebanx, bonjour @Dranreb ,

Deux fonctions, c'est une de trop! Une nouvelle fonction qui traite aussi les range vides. Cette fonction utilise un paramètre optionnel.

Function DerLig&(xrg As Range, Optional AbsRel&)
' si AbsRel est positif, nul ou omis
' => on renvoie le n° absolu de ligne (par rapport à la ligne 1 de la feuille)
' si AbsRel est strictement négatif
' => on renvoie le n° relatif de ligne (par rapport à la ligne 1 de xrg)
' si que des valeurs "vides", caractère vide ou erreur, on renvoie 0

Dim n1, n2
  On Error Resume Next
  n1 = Application.Match(1E+99, xrg(1, 1).EntireColumn)
  n2 = Application.Match(String(99, "z"), xrg(1, 1).EntireColumn)
  If IsError(n1) And IsError(n2) Then Exit Function
  If IsError(n1) Then n1 = xrg.Row
  If IsError(n2) Then n2 = xrg.Row
  If n2 > n1 Then n1 = n2
  Do While IsError(xrg(n1 - xrg.Row + 1, 1)) Or (xrg(n1 - xrg.Row + 1, 1) = "")
    n1 = n1 - 1: If n1 <= 0 Then Exit Do
  Loop
  If n1 > 0 Then
    If IsMissing(AbsRel) Or AbsRel >= 0 Then DerLig = n1 Else DerLig = n1 - xrg.Row + 1
  End If
End Function

 

[zebanx]

Re-

Par rapport à la dernière fonction proposée par @mapomme, sa variante en absolue si on travaille sur les lignes avec une fonction dercol()

Code et fichier

Encore merci à tous les deux pour votre rapidité et la grande qualité de vos réponses.

Bon apm

Function DerCol&(xrg As Range, Optional AbsRel&)
' si AbsRel est positif, nul ou omis
' => on renvoie le n° absolu de ligne (par rapport à la ligne 1 de la feuille)
' si AbsRel est strictement négatif
' => on renvoie le n° relatif de ligne (par rapport à la ligne 1 de xrg)
' si que des valeurs "vides", caractère vide ou erreur, on renvoie 0

Dim n1, n2
  On Error Resume Next
  n1 = Application.Match(1E+99, xrg(1, 1).EntireRow)
  n2 = Application.Match(String(99, "z"), xrg(1, 1).EntireRow)
  If IsError(n1) And IsError(n2) Then Exit Function
  If IsError(n1) Then n1 = xrg.Column
  If IsError(n2) Then n2 = xrg.Column
  If n2 > n1 Then n1 = n2
  Do While IsError(xrg(1, n1 - xrg.Column + 1)) Or (xrg(1, n1 - xrg.Column + 1) = "")
    n1 = n1 - 1: If n1 <= 0 Then Exit Do
  Loop
  If n1 <= 0 Then
    DerCol = 0
  Else
    If IsMissing(AbsRel) Or AbsRel >= 0 Then DerCol = n1 Else DerCol = n1 - xrg.Column + 1
  End If
End Function