Problème avec Formula, Index et EQUIV

Version imprimable

Bonjour,

J'essaye de finaliser une macro avec dedans les fonctions Index et equiv.
Les difficultés sont que je souhaite utiliser formula pour avoir la formule dans la cellule et j'utilise des variables.

Le reste du code fonctionne, la ligne qui bug est celle avec index.

Le code

Ci-dessous le code
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 For g = 9 To 300 While Cells(g, 3) <> "" Cells(g, e + 2).Formula = "=Min(" & Range(Cells(g, 5), Cells(g, e - 1)).Address & ")" Cells(g, e + 3).Formula = "=Max(" & Range(Cells(g, 5), Cells(g, e - 1)).Address & ")" If Cells(g, e).Value <> 0 And Cells(g, e).Value <> "" And Cells(g, e + 3).Value <> 0 Then Cells(g, e + 5).Value = (Cells(g, e + 3).Value) / (Cells(g, e).Value) Cells(g, e + 3).Formula = "=INDEX(" & Range(Cells(8, 5), Cells(8, e - 1)): Range(Cells(g, 5), Cells(g, e - 1)) & ",1," & "MATCH("&Cells(g, e + 3).value", & Range(Cells(g, 5), Cells(g, e - 1) &",0))" End If g = g + 1 Wend Next
En vous remerciant par avance

26/04/2018, 14h40
parmi

Bonjour,

2 choses:

- Que vaut e lorsque tu passes à cette ligne?
- Donne-nous la formule telle que tu l'inscrirais en français dans la barre de formule
26/04/2018, 16h21
Menhir

Regarde ton code : toute la fin est en rouge.
Ca, c'est mauvais signe.

L'éditeur de ce forum ne comporte pas (pas encore ?) de débugueur VBA mais il indique en rouge ce qui est entre guillemets.
Si la zone rouge se poursuit au delà de ce qui était prévu, c'est que les guillemets sont en nombre impaire, ce qui est rarement fonctionnel.

A mon avis, il en manque un juste avant ton dernier & Range.

Mais il est étonnant que le VBE ait laissé passer ça (ce que confirme le value sans majuscule qui précède) ...
27/04/2018, 06h34
pijaku

Bonjour,
Lors de concaténation avec &, ne surtout pas omettre l'espace avant le &.
"MATCH" & Cells....
27/04/2018, 06h37
pijaku

J'ajoute également que le signe : au milieu des deux Range me semble étrange. Tout comme les 2 Range utilisés (au lieu de leurs propriétés Address).....

Bonjour,

Excusez moi pour le délai de réponse.

Pour parmi:
e = Cells(8, Columns.Count).End(xlToLeft).Column

Je souhaiterai écrire =INDEX($E$8:$H$10;1;EQUIV(L9;E9:H9;0)) sachant que mes variables interviennent (jamais le même nombre de colonnes d'où l'intervention de e et pouvoir le faire sur plusieurs ligne avec g).

Pour menhir:
Il y avait bien un problème de synthaxe, j'arrive maintenant à lancer le code, cependant il bug sur la ligne en question.

Pour pijaku:

Penses tu qu'il faut utiliser le .address?

Voilà où en est le code:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 For g = 9 To 300 While Cells(g, 3) <> "" Cells(g, e + 2).Formula = "=Min(" & Range(Cells(g, 5), Cells(g, e - 1)).Address & ")" Cells(g, e + 3).Formula = "=Max(" & Range(Cells(g, 5), Cells(g, e - 1)).Address & ")" If Cells(g, e).Value <> 0 And Cells(g, e).Value <> "" And Cells(g, e + 3).Value <> 0 Then Cells(g, e + 5).Value = (Cells(g, e + 3).Value) / (Cells(g, e).Value) Cells(g, e + 3).Formula = "=INDEX(" & Range(Cells(8, 5), Cells(8, e - 1)) & ":" & Range(Cells(g, 5), Cells(g, e - 1)) & ",1," & "MATCH(" & Cells(g, e + 3).Value & "," & Range(Cells(g, 5), Cells(g, e - 1)) & ",0))" End If g = g + 1 Wend Next
En vous remerciant,

30/04/2018, 16h29
Menhir

Citation:

Envoyé par Alex86

Il y avait bien un problème de synthaxe, j'arrive maintenant à lancer le code, cependant il bug sur la ligne en question.

Quelle ligne ?
Quel message d'erreur ?
30/04/2018, 16h43
Alex86

Citation:

Envoyé par Menhir

Quelle ligne ?
Quel message d'erreur ?

Ligne comportant INDEX

Code:

Cells(g, e + 3).Formula = "=INDEX(" & Range(Cells(8, 5), Cells(8, e - 1)) & ":" & Range(Cells(g, 5), Cells(g, e - 1)) & ",1," & "MATCH(" & Cells(g, e + 3).Value & "," & Range(Cells(g, 5), Cells(g, e - 1)) & ",0))"

Le message d'erreur est le suivant:

Erreur d’exécution '13':
Incompatibilité de type
14/05/2018, 16h52
Alex86

Pas de solution ? :(
14/05/2018, 17h23
parmi

Tu pourrais déterminer des plages que tu mets dans des variables et les utiliser dans ta formule.
Ceci dit, aurais-tu un exemple de données non confidentielles que tu pourrais fournir en fichier SANS macro et le résultat escompté?
14/05/2018, 18h57
Menhir

A mon avis, tu confonds cellules et adresse de cellule.
De plus, je ne comprends pas la première référence de ton INDEX : une zone & ":" & une zone ????

Mais ne sachant pas ce que contiennent tes cellules, difficile d'en avoir la certitude.

Si j'ai raison, ceci devrait mieux fonctionner :

Code:

Cells(g, e + 3).Formula = "=INDEX(E8" & Cells(g, e - 1).Adrress & ",1," & "MATCH(" & Cells(g, e + 3).Value & "," & Range(Cells(g, 5), Cells(g, e - 1)).Adrress & ",0))"
14/05/2018, 19h11
parmi

C'est ce que je pense aussi Menhir,

Mais je modifierais le "=INDEX(E8" en "=INDEX(E8:"
ainsi que Address (au lieu de Adrress) ;)
17/05/2018, 17h25
Alex86

1 pièce(s) jointe(s)

Après essais du code de menhir avec les corrections de Parmi ça ne fonctionne toujours pas.

Vous trouverez ci-joint l'excel avec l'exemple de ce que je veux faire (la colonne problématique dans le code est la M). Pour les variables (g et e correspondent aux paramètres qui peuvent être plus nombreux ainsi que les numéros).

En vous remerciant par avance.
12/06/2018, 09h47
Alex86

Pas de solution avec l'excel ? :?
12/06/2018, 10h01
Menhir

Etant donné que beaucoup de participants à ce forum n'ouvrent pas les pièces jointes, il serait plus judicieux de répondre aux questions posées que de transmettre ton fichier.

De plus "ça ne fonctionne pas" n'est pas très explicite pour décrire un dysfonctionnement.
12/06/2018, 11h04
Alex86

Le message d'erreur est toujours le suivant:
Erreur d’exécution '13':
Incompatibilité de type

J'ai bien du mal avec cette ligne pour les adresses de cellules et les cellules

Citation:

De plus, je ne comprends pas la première référence de ton INDEX : une zone & ":" & une zone ????

Pour cette référence je souhaite faire une plage de cellule entre ces cellules

Code:

Range(Cells(8, 5), Cells(8, e - 1))

et celles-ci

Code:

Range(Cells(g, 5), Cells(g, e - 1)

C'est peut être ici que ça bloque.

Je remets le code

Code:

Cells(g, e + 3).Formula = "=INDEX(" & Range(Cells(8, 5), Cells(8, e - 1)) & ":" & Range(Cells(g, 5), Cells(g, e - 1)) & ",1," & "MATCH(" & Cells(g, e + 3).Value & "," & Range(Cells(g, 5), Cells(g, e - 1)) & ",0))"

Essaie comme ceci

Code:

1
2
3
4
5
6
7
8
    Dim nbLignes As Long
 
    With Sheets("Feuil1")
        nbLignes = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Range("K9:K" & nbLignes).Formula = "=MIN(E9:H9)"
        .Range("L9:L" & nbLignes).Formula = "=MAX(E9:H9)"
        .Range("M9:M" & nbLignes).Formula = "=IFERROR(INDEX($E$8:I" & nbLignes & ",1,MATCH(L9,E9:H9,0)),"""")"
    End With

12/06/2018, 15h23
Alex86

Merci parmi, le code fonctionne pour l'excel que j'ai fourni.
Cependant, mon nombre de colonne (nombre 1, 2... dans l'excel) peut varier aussi. Comment modifier la suite?

Comme ça peut-être?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Formules()
    Dim nbLignes As Long, nbColonnes As Long
 
    With Sheets("Feuil1")
        nbLignes = .Cells(.Rows.Count, "C").End(xlUp).Row
        nbColonnes = .Cells(8, Columns.Count).End(xlToLeft).Column - 6  '-6 s'il y a des entêtes
        .Range(.Cells(9, nbColonnes + 3), .Cells(nbLignes, nbColonnes + 3)).Formula = _
                "=MIN(E9:" & Cells(9, nbColonnes).Address(False, False) & ")"
        .Range(.Cells(9, nbColonnes + 4), .Cells(nbLignes, nbColonnes + 4)).Formula = _
                "=MAX(E9:" & Cells(9, nbColonnes).Address(False, False) & ")"
        .Range(.Cells(9, nbColonnes + 5), .Cells(nbLignes, nbColonnes + 5)).Formula = _
                "=INDEX($E$8:" & .Cells(nbLignes, nbColonnes).Address(False, False) & _
                ",1,MATCH(" & .Cells(9, nbColonnes + 4).Address(False, False) & ",E9:" & .Cells(9, nbColonnes).Address(False, False) & ",0))"
 
    End With
End Sub

12/06/2018, 16h48
Alex86

Exactement, parfait.

Merci parmi je classe en résolu ! :D