Erreur sur la macro VBA alors que sans VBA la cellule d'Excel donne le bon résultat. ** Gnack ^^ **

**Barba** · 29/10/2019, 12h36

Bonsoir les ami(e)s (oui il fait nuit au vietnam

)

j'ai utilisé l'enregistreur de macro (Excel 2013) pour marquer dans une cellule "AE6" une formule, qu'il copie et qu'il va copier en mode valeur sur une autre cellule P2

Hors lorsque j'utilise excell, il n'y a vraiment aucun problème : la cellule m'indique bien ce que je recherche soit : 0,26

Mais ... lorsque je lance la macro ... il me dit ... : *****...***** (j'ai du censurer tellement ce n'est pas agréable à entendre... ahaha)

En gros la macro sur la cellule "AE6" va chercher sur la feuille "JSON", colonne "A:A", où se situe la ligne de cette feuille qui contient les informations "energy_per_min" et extrait l'information de la cellule qu'il trouve

en gros il extrait ca :

Code JSON :

Sélectionner tout - Visualiser dans une fenêtre à part

    "energy_per_min": 0.26,

la formule me calcule où se situe ":" et me donne le nombre de caractère où se situe l'information (car le nombre 0,26 peut changer ca peut être par exemple 3000 comme 0,1)
de là ... il extrait le reste, calcul le nombre de caractère restant, calcule où est le caractère "," et le déduit, pour enfin avoir le résultat 0,26 qu'il transforme en valeur numérique.

(pouah .. j'espère que ce n'est pas trop compliqué à comprendre

)

voici la macro tiré de la formule écrite sur la cellule AE6 d'Excel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
                    '   Energie / minute
                    Range("AE6").Select
                    ActiveCell.FormulaR1C1 = _
                        "=NUMBERVALUE(MID((INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)),(FIND("":"",(INDEX(Json!C[-30],##(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)),1))+2,((LEN((INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)))-2)-(FIND("":"",(INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1" & _
                        ")),""."")"
                        Selection.Copy
                    Range("P2").Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False

j'ai essayé de mettre le code VBA sous balise, mais à priori je n'y arrive pas (excusez moi ^^)

=> je colle en "valeur" sur la cellule P2 car après avoir extrait les infos qui m'intéressent, je supprime la colonne "A:A" de la Feuille "JSON", donc si je marque sur la feuille excel sur la cellule P2 directement la formule, ca m'indique une erreur.

(forcement

)

et je supprime la colonne "A:A" pour alléger considérablement le fichier Excel (cette colonne comporte plus de 123 000 ligne sinon... et le fichier excel plusieurs Méga Octets ... )

=> je ne comprends vraiment pas d'où vient le problème, puisque en utilisant excel sans macro... tout fonctionne et il m'indique bien sur la cellule AE6 : 0,26

alors, j'ai fait un test sur la feuille excel :

Feuille JSON ligne (cellule BA3): =EQUIV("*"&"energy_per_min"&"*";Json!A:A;0),
ça m'indique bien la ligne 17 de la feuille JSON colonne A:A
ce qui est correct

Feuille JSON texte (cellule BA4): = (INDEX(Json!A:A;(EQUIV("*"&"energy_per_min"&"*";Json!A:A;0));1;1)),
ça m'indique bien le texte recherché soit : "energy_per_min": 0.26,

Position du caractère *:* (cellule BA7): = (TROUVE(":";BA4;1)),
ça m'indique la valeur correcte : 21

Nombre total de caractère - "," (cellule BA9): = (NBCAR(BA4)-2),
ça m'indique la valeur correcte : 25
(-2 car j'ai à priori des espaces dans la chaine de caractères)

Test final : = VALEURNOMBRE(STXT(BA4;BA7+2;(BA9-BA7));"."),
et là ça m'indique bien en numéraire: 0,26

j'ai remplacé les valeurs des différentes cellules par leurs formules respectives :

Copie ta formule (cellule BA13) : =VALEURNOMBRE(STXT((INDEX(Json!A:A;(EQUIV("*"&"energy_per_min"&"*";Json!A:A;0));1;1));(TROUVE(":";(INDEX(Json!A:A;(EQUIV("*"&"energy_per_min"&"*";Json!A:A;0));1;1));1))+2;((NBCAR((INDEX(Json!A:A;(EQUIV("*"&"energy_per_min"&"*";Json!A:A;0));1;1)))-2)-(TROUVE(":";(INDEX(Json!A:A;(EQUIV("*"&"energy_per_min"&"*";Json!A:A;0));1;1));1))));".")
=> là cela m'indique bien : 0,26

mais lorsque je l'enregistre en macro via l'enregistreur, ça me fait une grosse erreur

même en supprimant le retour de ligne, la macro m'indique une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
                    Range("AE6").Select
                    ActiveCell.FormulaR1C1 = _
                        "=NUMBERVALUE(MID((INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)),(FIND("":"",(INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)),1))+2,((LEN((INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1,1)))-2)-(FIND("":"",(INDEX(Json!C[-30],(MATCH(""*""&""energy_per_min""&""*"",Json!C[-30],0)),1" & ")),""."")"

peut-on faire un truc du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
a = "energy_per_min"   (comme ca je peux l'adapter à n'importe quelle recherche)
b = EQUIV("*"& a &"*";Json!A:A;0)
c = (INDEX(Json!A:A;(EQUIV("*"& a &"*";Json!A:A;0));1;1))
d = (TROUVE(":"; c ;1))
e = (NBCAR( c )-2)
f = VALEURNOMBRE(STXT(BA4;BA7+2;(BA9-BA7));".")  => VALEURNOMBRE(STXT(c;d+2;(e-d);".")

est-ce possible ?

Par avance merci pour toute l'aide que vous pourrez m'apporter (et je vous souhaite une agréable fin de journée

)

**lucasgaetan** · 29/10/2019, 17h14

Bonjour Barba,

Pour mettre le code entre balise il faut cliquer sur le bouton # et mettre le code entre ['CODE] et ['/CODE] (j'ai ajouté un apostrophe)

Sinon écrire des formules en VBA est compliqué, je préfère écrire la valeur en dure et relancer la macro au besoin

voila ce que je te propose

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub test()
'trouver la première cellule avec "energy_per_min", ne fonctionne que si cellule visible
Dim maFeuil As Worksheet
Dim maCel As Range
    Set maFeuil = ThisWorkbook.Sheets("JSON")
    Set maCel = maFeuil.Range("A:A").Find("energy_per_min", LookIn:=xlValues)
 
'Extraire les données dernier ":"
Dim pos2pt As Long
    'InStr = donne la position du caractere ":"
    pos2pt = InStr(maCel.Value, ":")
Dim maVal As String
    'Replace(xxx, ".", ",") = remplace "." par ","
    'Trim = enleve les espaces
    'Len(maCel.Value) - pos2pt = le nombre de carateres deriere les ":"
    maVal = Replace(Trim(Right(maCel.Value, Len(maCel.Value) - pos2pt)), ".", ",")
 
'renseigne la cellule "AE6"
    maFeuil.Range("AE6").Value = maVal
 
End Sub

**Barba** · 29/10/2019, 17h35

Coucou lucas ou gaetan

La feuille sur laquelle on doit coller les résultats s'appelle "A"
du coup je change le nom "ma feuil" stp ?

ca là il ne colle rien en P2 du coup (anciennement AE6)

tu le trouves où

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 Sub EnergieParMin()
    'trouver la première cellule avec "energy_per_min", ne fonctionne que si cellule visible
    Dim maFeuil As Worksheet
    Dim maCel As Range
        Set maFeuil = ThisWorkbook.Sheets("JSON")
        Set maCel = maFeuil.Range("A:A").Find("energy_per_min", LookIn:=xlValues)
 
    'Extraire les données dernier ":"
    Dim pos2pt As Long
        'InStr = donne la position du caractere ":"
        pos2pt = InStr(maCel.Value, ":")
    Dim maVal As String
        'Replace(xxx, ".", ",") = remplace "." par ","
        'Trim = enleve les espaces
        'Len(maCel.Value) - pos2pt = le nombre de carateres deriere les ":"
        maVal = Replace(Trim(Right(maCel.Value, Len(maCel.Value) - pos2pt)), ".", ",")
 
    'renseigne la cellule "AE6"
    maFeuil.Range("P2").Value = maVal
 
End Sub

après la suite du code Vba supprime la colonne A de la feuille JSON... mais là rien ne se passe à la cellule P2 de la feuille "A"
Par contre sur la feuille JSON en cellule O2 j'ai cela (alors que j'ai écris "P2" ahaha:
0,26,

en mettant ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Sub EnergieParMin()
    'trouver la première cellule avec "energy_per_min", ne fonctionne que si cellule visible
    Dim maFeuil As Worksheet
    Dim maCel As Range
        Set maFeuil = ThisWorkbook.Sheets("JSON")
        Set maCel = maFeuil.Range("A:A").Find("energy_per_min", LookIn:=xlValues)
 
    'Extraire les données dernier ":"
    Dim pos2pt As Long
        'InStr = donne la position du caractere ":"
        pos2pt = InStr(maCel.Value, ":")
    Dim maVal As String
        'Replace(xxx, ".", ",") = remplace "." par ","
        'Trim = enleve les espaces
        'Len(maCel.Value) - pos2pt = le nombre de carateres deriere les ":"
        maVal = Replace(Trim(Right(maCel.Value, Len(maCel.Value) - pos2pt)), ".", ",")
 
    'renseigne la cellule "AE6"
    Set maFeuil = ThisWorkbook.Sheets("A")
    maFeuil.Range("P2").Value = maVal
 
End Sub

j'ai bien sur la feuille "A" et cellule P2 : 0,26,
mais il y a la "," qu'il faudrait supprimer et donner au résultat une valeur numérique stp

merci encore pour ton aide

**MarcelG** · 29/10/2019, 17h54

Bonsoir,

Beaucoup de choses à dire.

L'enregistreur de macro peut rendre service, à cette condition qu'il faut en épurer le retour de toutes les méthodes Select et autres Activate.
Ce qui permet de rendre le code plus lisible.

D'autre part, à mon avis, il faut éviter les FormulaR1C1 qui sont vite illisibles.
Préférer la méthode Formula.

Pour reporter la formulation d'une cellule dans un code, voici une astuce bien simple:
- Ecrire la formule dans la cellule (exemple en A1), en contrôlant son résultat
- Dans l'éditeur VBE, afficher la fenêtre Exécution (CTRL + G)
- Au sein de celle-ci, écrire

?Range("A1").Formula

(ne pas oublier le "?")
puis activer touche Entrée .

On obtient alors une formulation que l'on peut reprendre dans un code.

Cela dit, je rejoins ce qui a été formulé précédemment.
Mieux vaut à mon sens "dérouler" quelques valeurs en VBA pour atteindre un résulta final.

Enfin, pour ne considérer qu'une partie de cellule délimitée par un séparateur, on peut utiliser une variable tableau Split

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim lachaine As String
lachaine = "Valpolicella:Roma"
 
Dim carac As Variant
carac = Split(lachaine,":")(0)
 
Debug.Print carac

La fenêtre Exécution retournera "Valpolicella".
Ici l'indice est 0. Avec 1, le résultat aurait été "Roma"

Voilà quelques pistes.

**Barba** · 29/10/2019, 18h08

merci Marcel pour tes conseils et ta réponse,

mais j'ai besoin de la première "," mais pas celle à la fin

aurais tu une astuce stp à partir de ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Sub EnergieParMin()
    'trouver la première cellule avec "energy_per_min", ne fonctionne que si cellule visible
    Dim maFeuil As Worksheet
    Dim maCel As Range
        Set maFeuil = ThisWorkbook.Sheets("JSON")
        Set maCel = maFeuil.Range("A:A").Find("energy_per_min", LookIn:=xlValues)
 
    'Extraire les données dernier ":"
    Dim pos2pt As Long
        'InStr = donne la position du caractere ":"
        pos2pt = InStr(maCel.Value, ":")
    Dim maVal As String
        'Replace(xxx, ".", ",") = remplace "." par ","
        'Trim = enleve les espaces
        'Len(maCel.Value) - pos2pt = le nombre de carateres deriere les ":"
        maVal = Replace(Trim(Right(maCel.Value, Len(maCel.Value) - pos2pt)), ".", ",")
 
    'renseigne la cellule "AE6"
    Set maFeuil = ThisWorkbook.Sheets("A")
    maFeuil.Range("P2").Value = maVal
 
End Sub

Par contre je ne peux pas garder la formule lié à la feuille JSON puisque la suite du code supprime la colonne A:A pour limiter la taille du fichier excel en question (je gagne 1Mo)

**MarcelG** · 29/10/2019, 18h23

Salut,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Public Sub essai_chaine()
 
Dim lachaine As String
lachaine = "marcel:mercatog.philippe.theze.ouskelnor"
 
Dim valch As String
valch = Replace(Split(lachaine, ":")(1), ".", ",")
 
Debug.Print valch
 
End Sub

Résultat:

mercatog,philippe,theze,ouskelnor

Tu peux consulter
- L'aide Microsoft sur la fonction Split
- La manipulation des chaînes de caractères par SilkyRoad

**Barba** · 29/10/2019, 18h26

Ca ne marche pas , j'ai ce code la marcel stp

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Sub EnergieParMin()
    'trouver la première cellule avec "energy_per_min", ne fonctionne que si cellule visible
    Dim maFeuil As Worksheet
    Dim maCel As Range
        Set maFeuil = ThisWorkbook.Sheets("JSON")
        Set maCel = maFeuil.Range("A:A").Find("energy_per_min", LookIn:=xlValues)
 
    'Extraire les données dernier ":"
    Dim pos2pt As Long
        'InStr = donne la position du caractere ":"
        pos2pt = InStr(maCel.Value, ":")
    Dim maVal As String
        'Replace(xxx, ".", ",") = remplace "." par ","
        'Trim = enleve les espaces
        'Len(maCel.Value) - pos2pt = le nombre de carateres deriere les ":"
        maVal = Replace(Trim(Right(maCel.Value, Len(maCel.Value) - pos2pt)), ".", ",")
 
    'renseigne la cellule "AE6"
    Set maFeuil = ThisWorkbook.Sheets("A")
    maFeuil.Range("P2").Value = maVal
 
End Sub