VBA problème de référence absolue dans le calcul de pourcentages

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

Je suis débutant en VBA et après des recherches infructueuses, je n’ai d’autre option que de demander de l’aide. Merci d’avance, donc, à ceux qui m’apporteront leur soutien.

Via une macro, dans un tableau Excel (2007), je souhaite calculer les pourcentages de chaque ligne. Cela implique d’avoir dans la formule la cellule des totaux en référence absolue et les autres cellules en référence relatives (soit =D4/$C$L). Or, le nombre de lignes du tableau est variable d’une fois sur l’autre (donc impossible de figer C et L).

J’ai essayé le code ci-dessous mais la formule dans Excel devient =D4/ADRESSE(5:5;D:D) {le smiley s'est invité sans être convié, je n'ai pas réussi à m'en débarrasser, en fait lire D: D sans l'espace} avec le message d’erreur #VALEUR !
Code:

1 2 3 4 5 Range("D4").End(xlDown).Offset(-1, 1).Select ActiveCell.FormulaR1C1 = "=RC[-1]/ADDRESS(R[+1],C[-1])" ActiveCell.Copy Range(ActiveCell, "E4").Select ActiveSheet.Paste
J’ai ensuite voulu faire le calcul « dans le code » pour ensuite afficher le résultat dans les cellules d’Excel avec le code suivant :
Code:

1 2 3 4 5 6 7 8 9 10 Dim NbLigne As Long, i As Long, j As Long NbLigne = Range("D" & Rows.Count).End(xlUp).Row i = NbLigne j = NbLigne Range("D4").End(xlDown).Select ActiveCell.Offset(-1, 1).Select For i = NbLigne To 4 Step -1 ActiveCell = Range(ActiveCell.offest(-1, 0)).Value / j ActiveCell.Offset(-1, 0).Select Next i
mais alors le message d’erreur « Erreur 438 Propriété ou méthode non gérée par cet objet » est renvoyé sur la ligne 8.
Par ailleurs, je n’ai pas trouvé comment afficher la valeur. J’ai essayé avec [ActiveCell] = mais alors le message d’erreur « Erreur 438 Propriété ou méthode non gérée par cet objet » est renvoyé.

Finalement mon problème se résume aux questions :
- comment dans VBA gérer ce problème de référence absolue dans une formule Excel avec un tableau dont le nombre de lignes varie ?
- comment effectuer un calcul dans VBA et afficher le résultat dans une cellule d’Excel ?

Toute proposition de solution à chacune de ces questions sera extrêmement bienvenue et son auteur en sera chaleureusement remercié.

Cordialement

PS: le fichier joint n'a pas grand intérêt. Il est juste là pour éviter quelques lignes de saisie.

27/08/2014, 13h51
had4789

Bonjour,

Concrètement tu veux faire quoi?

Pourquoi ne pas le faire directement dans la cellule?

Pourquoi n'essais tu pas avec l'enregistreur de macro?
27/08/2014, 17h06
pétrin

1 pièce(s) jointe(s)

Bonjour had4789,

Le problème de la macro enregistrée est qu'elle génère une référence absolue valable pour la cellule contenant le total qui est de la forme R11C4. Par conséquent si la fois suivante mon total se trouve en R20C4 la macro ne tiendra pas compte des lignes 12 à 20 et les résultats seront faux.

Concrètement, chaque ligne de la colonne E doit contenir le résultat du calcul suivant (de la formule suivante) :
ligne 2 =D2/$D$8
ligne 3 =D3/$D$8
Ligne 4 = D4/$D$8

or c'est le $D$8 que je n'arrive pas à renseigner dans les cellules du tableau avec mon code. Si cette cellule était toujours la même je n'aurais pas de problème. Il faut que je renseigne une référence absolue qui change d'une fois sur l'autre (le nombre de ligne de mon tableau changeant d'une fois sur l'autre).

C'est aussi parce que je dois utiliser fréquemment un fichier qui évolue et qu'à chaque fois je dois refaire les même opérations que je souhaite utiliser une macro.

j'ai de nouveau joints le fichier mais contenant cette fois les formules que je souhaite renseigner par macro.

J'espère avoir été plus clair.

Merci.
28/08/2014, 08h56
Menhir

Citation:

Envoyé par pétrin

Or, le nombre de lignes du tableau est variable d’une fois sur l’autre (donc impossible de figer C et L).

La réponse est dans la question.
Il te suffit, dans ton code, de récupérer la longueur de ton tableau et d'adapter tes formules en fonction de cette valeur.

Si la taille de ton tableau est telle qu'il t'oblige à dépasser la colonne Z, je te conseille de passer en références L1C1 plutôt que A1, ça te facilitera grandement la vie.
Options Excel > Formules > Cocher "Style de référence L1C1"

Bonjour,

Voila une macro qui fait ce que tu veux. Tu n'as plus qu'à changer le nom de la feuille.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Sheets("Feuil1").Select
 
 
With ThisWorkbook.Sheets("Feuil1")
 
            For i = .Range("D" & .Rows.count).End(xlUp).Row To 1 Step -1
 
                        If .Range("D" & i).Value <> "" Then
                                   .Range("E" & i).Value = .Range("D" & i).Value / .Range("D8").Value
 
                        End If
            Next i
End With
 
End Sub

Si tu veux connaître le nombre de ligne totale de ton tableau, il te suffit de prendre la première valeur prise par i dans ma formule (car je commence par le bas).

Du pourra ainsi faire D2/i etc...

28/08/2014, 10h04
Invité

Bonjour,

R donne la ligne C la colonne D8= R8C4!

Code:

ActiveCell.FormulaR1C1="=RC[-1]/R8C4"
28/08/2014, 11h25
pétrin

Bonjour Menhir, bonjour had4789

Menhir,

Non la réponse n’est pas dans la question. Mais sans doute ai-je manqué de clarté.

Tout d’abord le nombre de colonne est figé, donc par de problème de ce côté-là.

J’ai en fait omis de mentionner mon tout premier essai, lequel explique la présence des variables i et j : i doit me servir de compteur pour aller renseigner chaque ligne du tableau avec la formule qui me pose problème, j me sert à figer le nombre de lignes du tableau, l’idée étant de reprendre j dans la formule pour figer la cellule contenant le total.
Ma formule ressemblait à ActiveCell = "=Di/R & jC4"mais alors je retrouvais la chaîne de caractères dans Excel et non pas D2/$D$11 (D11 étant en dernière ligne et contenant le total de la ligne).
Je cherche donc à faire en sorte que mon code me permette d’avoir la formule avec la référence absolue dans la cellule d’Excel.

Concernant l’option d’Excel, si je coche cette option celle-ci sera active pour tout le fichier Excel seulement ou également pour tous les fichiers que je créerai ?
En tout cas, merci de t’être penché sur mon problème.

Menhir,

Merci pour le code. J’ai changé le nom de la feuille (ligne 5 et 8 de ton message) et malheureusement j’ai le message d’erreur suivant qui m’est renvoyé sur la ligne 8 :

Ligne 8 : With ThisWorkbook.Sheets("Alerte secteur")

Message d’erreur : Erreur d’exécution 9 : L’indice n’appartient pas à la sélection

Pourtant ma feuille « Alerte section » est ouverte. Je ne comprends pas. Et puis qu’est-ce que l’indice ?

Merci de ton aide.
28/08/2014, 11h43
SfJ5Rpw8

bonjour,

et la feuille "Alerte secteur" et bien dans le classeur qui contient ton code ?
28/08/2014, 11h46
Invité

attention aux espace avant et après double click sur le nom de l'onglet et copy colle
("Alerte secteur") c'est pas ("Alerte secteur ")
28/08/2014, 11h47
SfJ5Rpw8

tiens en relisant je vois que c'est pas "Alerte Secteur" mais "Alerte Section"...:!

Citation:

Ma formule ressemblait à ActiveCell = "=Di/R & jC4"mais alors je retrouvais la chaîne de caractères dans Excel et non pas D2/$D$11 (D11 étant en dernière ligne et contenant le total de la ligne).

Voila un code qui permet de diviser la valeur de la celulle D par la dernière valeur de la colonne D.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub test()
 
Application.ScreenUpdating = False
Dim i As Integer
Sheets("Feuil1").Select
 
With ThisWorkbook.Sheets("Feuil1")
 
            For i = .Range("D" & .Rows.count).End(xlUp).Row To 1 Step -1
 
                        If .Range("D" & i).Value <> "" Then
                                   .Range("E" & i).Value = .Range("D" & i).Value / .Range("D2").End(xlDown)
 
                        End If
            Next i
End With
 
End Sub

28/08/2014, 11h53
Menhir
Citation:

Envoyé par pétrin

Merci pour le code. J’ai changé le nom de la feuille (ligne 5 et 8 de ton message) et malheureusement j’ai le message d’erreur suivant qui m’est renvoyé sur la ligne 8 :

Ligne 8 : With ThisWorkbook.Sheets("Alerte secteur")

Message d’erreur : Erreur d’exécution 9 : L’indice n’appartient pas à la sélection

Pourtant ma feuille « Alerte section » est ouverte. Je ne comprends pas. Et puis qu’est-ce que l’indice ?

Essaye de faire tourner ce petit bout de code :
Code:

1 2 3 4 5 6 Sub ListeSheet() Dim S as Worksheet For Each S in ThisWorkbook.Worksheets Debug.Print S.Name Next S End Sub
Et regarde le nom de tes Worksheet dans la fenêtre exécution pour voir s'il n'y a pas une erreur d'orthographe.
Eventuellement, fais un copier/coller du nom qui t'intéresse vers ton code.
28/08/2014, 14h03
pétrin

Bonjour à ceux auxquels je n'ai pas encore répondu,

et merci de votre aide.

Menhir,
J’ai ouvert la feuille « Alerte secteur », j’ai fait tourner ton code et il me renvoie Feuil1. Est-ce à dire que le code « appelle » le classeur PERSONAL.xlsb ? Et si c’est la cas, avec l’instruction
Sheets("Alerte secteur").Select
C’est la feuille « Alerte secteur » qui devrait être appelée, non ? Je ne comprends pas.

rdurupt,
si j'écris R8C4 dans ma formule je fige la dimension de mon tableau, or le nombre de lignes est variable d'une fois sur l'autre. Je n’ai que 4 colonnes dont le nombre est figé, donc pas de problème de ce côté là.

bbil + rdurupt,
désolé, il s'agit d'une erreur de saisie dans mon message, mais j'ai bien vérifié, j'ai bien écrit ("Alerte secteur") sans espace en trop ou manquant. Par contre, mon code se trouve dans le classeur PERSONAL,xlsb et donc je pensais qu’il était accessible depuis tous les autres classeurs, donc de celui que j'utilise . Sans doute que non alors… du coup la manip proposée par Menhir devrait m’aider (mais j’aurai besoin d’une petite explication car en l’état je ne comprends pas). En fait, jusqu'ici tous mes codes sont sur PERSONAL.xlsb et je créé différents classeurs au fur et à mesure pour limiter le nombre de feuilles, d'autant plus que je suis obligé de faire beaucoup d'essais.

Merci à tous
28/08/2014, 14h08
SfJ5Rpw8

Citation:

Envoyé par bbil

bonjour,

et la feuille "Alerte secteur" et bien dans le classeur qui contient ton code ?

et donc ?
28/08/2014, 14h12
Menhir

Citation:

Envoyé par pétrin

J’ai ouvert la feuille « Alerte secteur », j’ai fait tourner ton code et il me renvoie Feuil1. Est-ce à dire que le code « appelle » le classeur PERSONAL.xlsb ? Et si c’est la cas, avec l’instruction
Sheets("Alerte secteur").Select
C’est la feuille « Alerte secteur » qui devrait être appelée, non ? Je ne comprends pas.

J'ai l'impression que ton ThisWorkbook ne pointe pas sur ce que tu crois...:weird:
Tu as bien mis mon petit bout de macro dans le même module que ta macro ?

Si tu veux être fixé sur la question, c'est pas compliqué. Ajoute :

Code:

Debug.Print ThisWorkbook.Name

juste avant de For Each de ma mini-macro.

Tu verras bien sur quoi pointe ton ThisWorkbook
S'il pointe vers un XLSB indésirable (comme tu sembles le soupçonner), ça veut dire soit que tu n'as pas placé ta macro au bon endroit (ThisWorkbook pointe sur le fichier dans lequel se trouve la macro), soit qu'il faudra utiliser autre chose que ThisWorkbook pour pointer sur le classeur que tu souhaites.
28/08/2014, 15h07
pétrin

Menhir,

J'ai fait ce que tu m'as proposé. En fait mon code pointe sur PERSONAL.xlsb. Il me faudra donc utiliser une autre instruction...mais laquelle ?

bbil,

et donc ? et bien je ne sais pas. J'ai répondu à ta remarque mais maintenant je ne sais toujours pas comment résoudre mon problème...

Merci de votre aide.
28/08/2014, 15h14
Menhir

Citation:

Envoyé par pétrin

J'ai fait ce que tu m'as proposé. En fait mon code pointe sur PERSONAL.xlsb.

ThisWorkbook pointe sur le classeur qui contient ta macro. Si tu as mis ta macro dans PERSONAL.xlsb, c'est sur ce fichier qu'elle pointe.

Citation:

Il me faudra donc utiliser une autre instruction...mais laquelle ?

De la même façon que tu utilises Worksheets(MaFeuille) pour pointer sur un onglet particulier, tu utiliseras Workbooks(MonFichier) pour pointer sur le bon fichier.

Tape Workbook dans la case de recherche et l'aide intégrée VBA Excel, tu verras comment utiliser cet objet.
28/08/2014, 15h22
NVCfrm
Citation:

Envoyé par pétrin

Bonjour,
....
Finalement mon problème se résume aux questions :
- comment dans VBA gérer ce problème de référence absolue dans une formule Excel avec un tableau dont le nombre de lignes varie ?
- comment effectuer un calcul dans VBA et afficher le résultat dans une cellule d’Excel ?
...

Bonjour,
ton problème se devine dans l'extrait de code que tu as posté.
Si tu testais les propriété d'ActiveCell au moment de l'erreur, tu comprendras.

Un peu comme dans les formules, les détails des références relatives ou absolues dans le tableur, en vba également il y a ces notions dans certaines variables.
Toutes les variables globales genre préfixées avec Active ou non comme Cells, Range... sont relatives à l'application Excel.
Elles renvoient au niveau application l'élément qui a le focus en quelque sorte.
La feuille active par exemple, n'est pas forcément là où ton code s’exécute.
La cellule active est renvoyée de la feuille active, qui est renvoyée de la fenêtre de classeur actif.
Ce sont des variables temporaires. Qui sont récrées à chaque changement de sélection.

WorkSheets("feuil1").Range("A1") renvoyant par exemple la cellule A1 du Classeur1 a un moment donné peut renvoyé la même correspondante a un Classeur2 contenant une feuille du même nom.
Pour palier à ce genre de bug, il faut de façon absolue dans un environnement multi-classeurs désigner le classeur.
WorkBooks("Classeur1").WorkSheets("feuil1").Range("A1")Je te dirais d'ajouter ces tests à la suite du test proposé par Menhir
Code:

1 2 3 Debug.Print ActiveCell.Worksheet.Name Debug.Print ActiveCell.Worksheet.Parent.Name Debug.Print ActiveCell.Address
Ton
29/08/2014, 17h27
pétrin

Bonjour NVCfrm, bonjour à tous,

Grâce à vous j’ai réussi à résoudre mon problème et je comprends un peu comment « coder ».

Un grand merci à chacun d’entre vous, donc !

Par curiosité, tout de même, y a-t-il moyen de renseigner les cellules d’Excel avec la formule contenant des références relatives comme décrit dans ma réponse du 27/08/2014, 17h06 ? j’ai pensé à passer par des concaténations mais je crains que cela ne fasse que déplacer le problème.

Cordialement