XL 2019 Ecrire Formule

jper · 28 Décembre 2021

Bonjour,
J'aurai besoin d'écrire une formule de recherchev avec traitement d'erreur dans une plage.
J'ai le code suivant

VB:

For I = 2 To NligMagRel - 1
        'Cells(I, 2).Formula = "=SI(ESTNA(RECHERCHEV(A2;$E$1:$F$25;2;FAUX));"";RECHERCHEV(A2;$E$1:$F$25;2;FAUX))"
        Cells(I, 2).Select
        'Cells(I, 2).Formula = "=si(estna(recherchev(A&i;range(cells(1,5),cells(L,6));2;faux));"";recherchev(A&i;range(cells(1,5),cells(L,6));2;faux))"
        Cells(I, 2).Formula = "=recherchev(A&i;range(cells(1,5),cells(L,6));2;faux)"
        
        'Cells(I, 2).Value = "=SI(ESTNA(RECHERCHEV(A2;$E$1:$F$25;2;FAUX));"";RECHERCHEV(A2;$E$1:$F$25;2;FAUX))"
        
    Next

J'ai fait plusieurs essais:
- avec le formule littérale (première ligne en commentaire)
- avec des "range"
- uniquement la formule de recherche
je n'ai pas mis de variable dans le zone de recherche car celle ci peut être variable en fonction de la longueur de la zone E:F

Tous mes essais se conclue par une erreur .
Pouvez-vous m'aider, car je désirer aussi écrire dans une cellule une formule Filtre qui fonctionne quand je l'écrit tout seul dans un fichier, mais là je désirai l'écrire en vbA.

D'avance merci,
Cordialement,

Hasco · 28 Décembre 2021

bonjour,

Votre fichier exemple est un .xlsx => pas de macro.
Si vous utilisez .Formula, votre formule doit être en anglais.
.FormulaLocal : langue de la machine

Tapez votre formule dans une cellule puis faites en vba :
Debug.Print ActiveCell.Formula
Après validation vous verrez s'afficher dans la fenêtre d'exécution(CTRL+G pour l'ouvrir )votre formule telle que vous devrez l'écrire.

Après ce n'est qu'adaptation de la chaîne de caratères (attention aux guillemets)

cordialement

Dranreb · 28 Décembre 2021

Bonjour.

VB:

Sub MagAvAno()
   Dim L&
   With ActiveSheet
      L = .[E1000000].End(xlUp).Row
      .[B2].Resize(.[A1000000].End(xlUp).Row - 1).FormulaR1C1 = "=IFERROR(INDEX(R2C6:R" _
         & L & "C6,MATCH(RC1,R2C5:R" & L & "C5,0)),"""")"
      End With
   End Sub

mapomme · 28 Décembre 2021

Bonjour @jper , @Hasco , @Dranreb ,

Votre fichier est un peu léger ,

Dans le Module1 du fichier joint, un exemple de ce qu'on peut faire.
On remplit les colonnes B et C de formules.
On utilise FormulaLocal qui permet d'utiliser le français la notation de type A1.
Le code s'adapte au nombre de ligne de la liste des magasins et de la liste des anomalies.
Le code est commenté.
C'est une piste...

VB:

Sub Formules()
Dim derligMag As Long, derligAno As Long, formuleB2 As String

   With Sheets("Export")
      'on affiche tout pour que les END(xlup) fonctionnent correctement
      If .FilterMode Then .ShowAllData
      'dernière ligne colonne A
      derligMag = .Cells(Rows.Count, "a").End(xlUp).Row
      'dernière ligne colonne E
      derligAno = .Cells(Rows.Count, "e").End(xlUp).Row
      'La formule en B2 en français
      formuleB2 = "=SIERREUR(RECHERCHEV(A2;$E$1:$F$25;2;FAUX);"""")"
      'le nombre de lignes des anomalies pouvant changer, on remplace 25 par la dernière ligne des anomalies
      formuleB2 = Replace(formuleB2, 25, derligAno)
      'on écrit la formule en B2
      .Cells(2, "b").FormulaLocal = formuleB2
      'On écrit la formule en C2 en français (ne dépend d'aucun nombre deligne)
      .Cells(2, "c").FormulaLocal = "=SI(B2="""";""ok"";"""")"
      'on étend les formules de B2:C2 jusqu'en fin de la liste des magasins
      .Range("b2:c2").AutoFill .Range("b2:c" & derligMag)
   End With
End Sub

Dranreb · 28 Décembre 2021

En garnissant aussi la C :

VB:

Sub MagAvAno()
   Dim N&, L&
   With ActiveSheet
      N = .[A1000000].End(xlUp).Row - 1
      L = .[E1000000].End(xlUp).Row
      .[B2].Resize(N).FormulaR1C1 = "=IFERROR(INDEX(R2C6:R" _
         & L & "C6,MATCH(RC1,R2C5:R" & L & "C5,0)),"""")"
      .[C2].Resize(N).FormulaR1C1 = "=IF(ISERROR(MATCH(RC1,R2C5:R" _
         & L & "C5,0)),""" & ChrW$(&H2714) & ""","""")"
      End With
   End Sub

jper · 29 Décembre 2021

Bonjour,
Merci pour vos réponses, la solution de "Ma Pomme" fonctionne super bien.
Cordialement,

XL 2019 Ecrire Formule

jper

XLDnaute Nouveau

Pièces jointes

Hasco

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

jper

XLDnaute Nouveau

Discussions similaires