Définir un range en VBA Comment définir en VBA une plage (range) allant de C3 à la dernière cellule remplie de la la colonne G ?
Il y a plusieurs manières de l'écrire.
La plus concise :
Range([C3], [G65635].End(xlUp))
la "dernière cellule remplie de la colonne G" est ici trouvée en partant du bas de la colonne G. Tu peux partir du haut s'il n'y a pas de cellules vides dans tes données. Dans ce cas, ton range s'écrit
Range([C3], [G1].End(xdown))
On peut bien entendu faire de même avec end(xlright) ou left pour trouver la dernière cellule remplie sur une ligne.
Cette façon d'écrire est en fait équivalente à
range(range("C3"), range("G65536").end(xlUp)).
tu peux donner un nom à ce range
toto=Range([C3], [G65635].End(xlUp))
ou encore appliquer une couleur aux cellules qu'il contient
Range([C3], [G65635].End(xlUp)).Interior.ColorIndex = 3
ou encore... faire 1000 choses !
La plus concise :
Range([C3], [G65635].End(xlUp))
la "dernière cellule remplie de la colonne G" est ici trouvée en partant du bas de la colonne G. Tu peux partir du haut s'il n'y a pas de cellules vides dans tes données. Dans ce cas, ton range s'écrit
Range([C3], [G1].End(xdown))
On peut bien entendu faire de même avec end(xlright) ou left pour trouver la dernière cellule remplie sur une ligne.
Cette façon d'écrire est en fait équivalente à
range(range("C3"), range("G65536").end(xlUp)).
tu peux donner un nom à ce range
toto=Range([C3], [G65635].End(xlUp))
ou encore appliquer une couleur aux cellules qu'il contient
Range([C3], [G65635].End(xlUp)).Interior.ColorIndex = 3
ou encore... faire 1000 choses !
Flo Cabon,
Ajouté ou modifié le 05/04/2008 (N°1997)
Ajouté ou modifié le 05/04/2008 (N°1997)
Comment en VBA affecter à une table les valeurs d'une plage tout en transposant les données ? Ainsi, les données d'une plage de taille L x C seraient affectées à une table de taille C x L.
c = Application.Transpose(Range("A1:C4"))
Pour transposer C vers la plage E10 ...
Range("E10").Resize(UBound(c, 1), UBound(c, 2)) = c
Pour transposer C vers la plage E10 ...
Range("E10").Resize(UBound(c, 1), UBound(c, 2)) = c
Denis Michon,
Ajouté ou modifié le 01/12/2007 (N°1938)
Ajouté ou modifié le 01/12/2007 (N°1938)
Je sais que le mot pomme est présent dans un tableau. Comment récupérer l'adresse de ce mot ? Je voudrais en quelque sorte l'inverse de la fonction Index.
Avec la plage de données contenant le mot à chercher nommée ici "Zn", et la valeur recherchée (pomme) nommée "reC", et en supposant que le tableau débute en A1
inscrit dans la cellule où tu veux récupérer l'adresse :
{="lig"&MIN(SI(Zn=reC;LIGNE(Zn)))&"/col"&MAX(SI(Zn=reC;COLONNE(Zn)))}
(à saisir sans les { } et valider par Ctrl+Shift+Entrée)
Si la plage de données est "décalée" par rapport à A1, par ex. début en C4
soit un"décallage" de 3 pour les lignes et 2 pour les colonnes
(à saisir sans les { } et valider par Ctrl+Shift+Entrée)
{="lig"&MIN(SI(Zn=reC;LIGNE(Zn)-3))&"/col"&MAX(SI(Zn=reC;COLONNE(Zn)-2))}
Si au lieu de récupérer l'adresse sous forme "lig.3/col.5" tu veux récupérer directement "E3"
utilise plutôt cette formule (à saisir sans les { } et valider par Ctrl+Shift+Entrée)
{=ADRESSE(MIN(SI(Zn=reC;LIGNE(Zn)));MAX(SI(Zn=reC;COLONNE(Zn)));4)}
inscrit dans la cellule où tu veux récupérer l'adresse :
{="lig"&MIN(SI(Zn=reC;LIGNE(Zn)))&"/col"&MAX(SI(Zn=reC;COLONNE(Zn)))}
(à saisir sans les { } et valider par Ctrl+Shift+Entrée)
Si la plage de données est "décalée" par rapport à A1, par ex. début en C4
soit un"décallage" de 3 pour les lignes et 2 pour les colonnes
(à saisir sans les { } et valider par Ctrl+Shift+Entrée)
{="lig"&MIN(SI(Zn=reC;LIGNE(Zn)-3))&"/col"&MAX(SI(Zn=reC;COLONNE(Zn)-2))}
Si au lieu de récupérer l'adresse sous forme "lig.3/col.5" tu veux récupérer directement "E3"
utilise plutôt cette formule (à saisir sans les { } et valider par Ctrl+Shift+Entrée)
{=ADRESSE(MIN(SI(Zn=reC;LIGNE(Zn)));MAX(SI(Zn=reC;COLONNE(Zn)));4)}
ChrisV,
Ajouté ou modifié le 27/10/2007 (N°1913)
Ajouté ou modifié le 27/10/2007 (N°1913)
En A1 j'écris = A2. Si j'insère une ligne entre A1 et A2, la référence en A1 devient =A3, et ceci que je mette ou non des $ : =$2 ne change rien. Comment faire toujours référence à le cellule située juste en dessous ?
remplace la formule =A2 par
=INDIRECT(ADRESSE(LIGNE()+1;COLONNE()))
=INDIRECT(ADRESSE(LIGNE()+1;COLONNE()))
Isabelle,
Ajouté ou modifié le 25/08/2007 (N°1892)
Ajouté ou modifié le 25/08/2007 (N°1892)
Comment remplacer dans une feuille toutes mes références relatives en références absolues ?
Utilise cette macro :
Sub DoAbsolut()
'Tom Ogilvy, mpep
Dim inputFormula As String
Dim outputFormula As String
Dim cell As Range
Dim rng As Range
Set rng = Selection ' ActiveSheet.UsedRange.SpecialCells(xlFormulas)
For Each cell In rng
inputFormula = cell.Formula
outputFormula = Application.ConvertFormula( _
Formula:=inputFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
ToAbsolute:=xlAbsolute)
cell.Formula = outputFormula
Next
End Sub
Sub DoAbsolut()
'Tom Ogilvy, mpep
Dim inputFormula As String
Dim outputFormula As String
Dim cell As Range
Dim rng As Range
Set rng = Selection ' ActiveSheet.UsedRange.SpecialCells(xlFormulas)
For Each cell In rng
inputFormula = cell.Formula
outputFormula = Application.ConvertFormula( _
Formula:=inputFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
ToAbsolute:=xlAbsolute)
cell.Formula = outputFormula
Next
End Sub
Tom Ogilvy,
Ajouté ou modifié le 25/08/2007 (N°1886)
Ajouté ou modifié le 25/08/2007 (N°1886)
Qu'est qu'une adresse absolue, une adresse relative et comment passer de l'un à l'autre ?
Prenons un exemple en simple : en C9 j'ai le texte "coucou" et en C10 le texte "adieu"
Si j'écris en D9 la formule = C9, "coucou" s'écrit en D9.
Je peux "tirer" cette formule vers le bas : avec la souris je pince le petit carré en bas à droite de la cellule et je descends d'une case. En D10 on voit s'inscrire "adieu". Si je clique dans cette cellule et que je regarde la forume qui est incrite, je lis = C10.
C'est l'exemple type des références relatives : en écrivant en D10 = C10, je dis à excel "recopie la valeur de la cellule qui est située un colonne à ma gauche et sur la même ligne que moi".
Quand je tire ma formule vers le bas, excel adapte la formule pour toujours garder la référence à la cellule située une colonne à gauche et sur la même ligne.
De même en écrivant en C10 = A1 on demande à excel de trouver la valeur de la cellule située 2 colonnes à gauche et 9 lignes au dessus.
C'est cette notion de référence relative qui a fait initialement tout l'intérêt des tableurs car cela permet d'écrire une fois une formule et de l'appliquer à toute une colonne par exemple sans avoir besoin de la recopier en l'adaptant. Mais parfois, on veut pouvoir figer dans une formule la référence à une cellule. Imaginons par exemple des valeurs en colonne A et que l'on veuille multiplier la valeur de chaque ellule de cette colonne par la valeur contenue dans la valeur D1. En B1 on écrit = A1*D1
Si on tire cette formule vers le bas, en B2 on aura = A2*D2 et comme D2 est vide, ça ne marchera pas.
Pour "figer" la référence à D1, on ajoute le signe $ :
=A1*$1 donnera en B10 : = A10*$1
le fait de mettre un signe $ devant la référence de la colonne (D) et de la ligne (1) fige les deux.
On peut tout à fait utiliser des références semi relatives et ne figer que le N° de ligne ou que celui de la colonne. Admettons par exemple que tu veuilles écrire
en AA1= A1*B1
en AB1= A1*C1
en AC1=A1*D1
... et ceci pour chaque valeur de la colonne A. Tu peux bien sur dans toutes les colonnes AA, AB... taper la bonne formule mais tu peux aussi bien plus simplement écrire en AA1:
=*B1 et ensuite tirer cette formule vers la droite puis sélectionner les cellules AA1, AB1... et tirer l'ensemble vers le bas. EN AD10 par exemple, tu trouveras comme formule :
=*D10.
Pour éviter d'user tes doigts sur la touche $, au risque de mal placer tes $ (qui doivent être placé toujours AVANT la référence à fixer: c$1$ ne marhe pas) tu peux avantageusement utiliser la touche F4. Si le curseur est placé dans la barre de formules dans une ref de cellule (H6), un clic sur F4 fige ligne et colonne ($6) un second renvoie H$6, un troisième et le 4° revient à l'écriture de départ : H6.
Si j'écris en D9 la formule = C9, "coucou" s'écrit en D9.
Je peux "tirer" cette formule vers le bas : avec la souris je pince le petit carré en bas à droite de la cellule et je descends d'une case. En D10 on voit s'inscrire "adieu". Si je clique dans cette cellule et que je regarde la forume qui est incrite, je lis = C10.
C'est l'exemple type des références relatives : en écrivant en D10 = C10, je dis à excel "recopie la valeur de la cellule qui est située un colonne à ma gauche et sur la même ligne que moi".
Quand je tire ma formule vers le bas, excel adapte la formule pour toujours garder la référence à la cellule située une colonne à gauche et sur la même ligne.
De même en écrivant en C10 = A1 on demande à excel de trouver la valeur de la cellule située 2 colonnes à gauche et 9 lignes au dessus.
C'est cette notion de référence relative qui a fait initialement tout l'intérêt des tableurs car cela permet d'écrire une fois une formule et de l'appliquer à toute une colonne par exemple sans avoir besoin de la recopier en l'adaptant. Mais parfois, on veut pouvoir figer dans une formule la référence à une cellule. Imaginons par exemple des valeurs en colonne A et que l'on veuille multiplier la valeur de chaque ellule de cette colonne par la valeur contenue dans la valeur D1. En B1 on écrit = A1*D1
Si on tire cette formule vers le bas, en B2 on aura = A2*D2 et comme D2 est vide, ça ne marchera pas.
Pour "figer" la référence à D1, on ajoute le signe $ :
=A1*$1 donnera en B10 : = A10*$1
le fait de mettre un signe $ devant la référence de la colonne (D) et de la ligne (1) fige les deux.
On peut tout à fait utiliser des références semi relatives et ne figer que le N° de ligne ou que celui de la colonne. Admettons par exemple que tu veuilles écrire
en AA1= A1*B1
en AB1= A1*C1
en AC1=A1*D1
... et ceci pour chaque valeur de la colonne A. Tu peux bien sur dans toutes les colonnes AA, AB... taper la bonne formule mais tu peux aussi bien plus simplement écrire en AA1:
=*B1 et ensuite tirer cette formule vers la droite puis sélectionner les cellules AA1, AB1... et tirer l'ensemble vers le bas. EN AD10 par exemple, tu trouveras comme formule :
=*D10.
Pour éviter d'user tes doigts sur la touche $, au risque de mal placer tes $ (qui doivent être placé toujours AVANT la référence à fixer: c$1$ ne marhe pas) tu peux avantageusement utiliser la touche F4. Si le curseur est placé dans la barre de formules dans une ref de cellule (H6), un clic sur F4 fige ligne et colonne ($6) un second renvoie H$6, un troisième et le 4° revient à l'écriture de départ : H6.
Flo Cabon,
Ajouté ou modifié le 25/08/2007 (N°1885)
Ajouté ou modifié le 25/08/2007 (N°1885)
En A1 j'ai écrit la formule =A2. J'insère ensuite une ligne en dessous de A1. La cellule A2 devient A3 mais j'aimerais pourtant que ma formule fasse toujours référence à A2. Comment faire ? Mettre $2 à la place de A2 ne résoud rien.
remplace la formule =A2 par
=INDIRECT(ADRESSE(LIGNE()+1;COLONNE()))
=INDIRECT(ADRESSE(LIGNE()+1;COLONNE()))
Isabelle,
Ajouté ou modifié le 14/07/2007 (N°1866)
Ajouté ou modifié le 14/07/2007 (N°1866)
J'ai utilisé le menu insertion/nom/défiinir pour nommer une plage. Comment utliser ce nom dans VBA
Pour sélectionner cette plage, il te suffit d'écrire
range("maplage").select
range("maplage").select
Flo Cabon,
Ajouté ou modifié le 13/07/2007 (N°1863)
Ajouté ou modifié le 13/07/2007 (N°1863)
Toutes les cellules de la plage A1:H30 contiennent une formule conditionnelle qui renvoie soit "" soit une valeur. Comment trouver l'adresse de la dernière cellule contenant une valeur dans cette plage ?
Cette question peut être interprétée de différentes façons (voir l'exemple joint à télécharger). Teste donc ces différntes solutions et choisis celle qui te convient le mieux:
Une formule matricielle ( à valider par ctrl+maj.entrée)
=ADRESSE(MAX(SI(Plage<>"";LIGNE(Plage)));MAX(SI(Plage<>"";COLONNE(Plage))))
Une autre :
=ADRESSE(MAX(SI(Plage<>"";LIGNE(Plage)));MAX(SI(Plage<>"";SI(LIGNE(Plage)=MAX(SI(Plage<>"";LIGNE(Plage)));COLONNE(Plage)))))
Une fonction VBA :
Function DerCell_NonVide(Plg As Range) As String
Dim DerLig As Long, DerCol As Integer
DerLig = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
DerCol = Plg.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
DerCell_NonVide = Cells(DerLig, DerCol).Address
End Function
Tu peux utiliser cette fonction dans la feuille de calcul : =DerCell_NonVide(A1:H30)
ou en VBA :
Sub test()
With Worksheets("Feuil1")
MsgBox DerCell_NonVide(.Range("A1:H30"))
End With
End Sub
******************************************************************
autre solution :
Sub dernièreligne()
Set x = [D9:Z30].Find("*", , xlValues, , xlByRows, xlPrevious)
MsgBox x.Address
End Sub
Sub dernièreColonne()
Set x = [A1:H30].Find("*", , xlValues, , xlByColumns, xlPrevious)
MsgBox x.Address
End Sub
Sub IntersectionDerLigneColonne()
x = Cells([A1:H30].Find("*", , xlValues, , xlByRows, xlPrevious).Row,
[D9:Z58].Find("*", , xlValues, , xlByColumns, xlPrevious).Column).Address
MsgBox x
End Sub
******************************************************************
ou bien encore :
If Not IsError([z]) Then Names("z").Delete
Names.Add Name:="z", RefersTo:="=MAX(IF(A1:H30<>"""",ROW(A1:H30)))"
MsgBox [z]
pour l'adresse de la cellule à l'intersection de la dernière ligne et de la dernière colonne,
If Not IsError([k]) Then Names("k").Delete
ActiveWorkbook.Names.Add Name:="k", RefersTo:= _
"=ADDRESS(MAX(IF(Feuil2!$9:$58<>"""",ROW(Feuil2!$9:$58))),MAX(IF(Feuil2!$9:$58<>"""",COLUMN(Feuil2!$9:$58))))"
MsgBox [k]
*********************************************************************
Et encore :
Sub Evaluate_AV()
MsgBox [address(max(if(Plage<>"",row(plage))),max(if(Plage<>"",if(row(Plage)=max(if(Plage<>"",row(Plage))),column(Plage)))))]
End Sub
Une formule matricielle ( à valider par ctrl+maj.entrée)
=ADRESSE(MAX(SI(Plage<>"";LIGNE(Plage)));MAX(SI(Plage<>"";COLONNE(Plage))))
Une autre :
=ADRESSE(MAX(SI(Plage<>"";LIGNE(Plage)));MAX(SI(Plage<>"";SI(LIGNE(Plage)=MAX(SI(Plage<>"";LIGNE(Plage)));COLONNE(Plage)))))
Une fonction VBA :
Function DerCell_NonVide(Plg As Range) As String
Dim DerLig As Long, DerCol As Integer
DerLig = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
DerCol = Plg.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
DerCell_NonVide = Cells(DerLig, DerCol).Address
End Function
Tu peux utiliser cette fonction dans la feuille de calcul : =DerCell_NonVide(A1:H30)
ou en VBA :
Sub test()
With Worksheets("Feuil1")
MsgBox DerCell_NonVide(.Range("A1:H30"))
End With
End Sub
******************************************************************
autre solution :
Sub dernièreligne()
Set x = [D9:Z30].Find("*", , xlValues, , xlByRows, xlPrevious)
MsgBox x.Address
End Sub
Sub dernièreColonne()
Set x = [A1:H30].Find("*", , xlValues, , xlByColumns, xlPrevious)
MsgBox x.Address
End Sub
Sub IntersectionDerLigneColonne()
x = Cells([A1:H30].Find("*", , xlValues, , xlByRows, xlPrevious).Row,
[D9:Z58].Find("*", , xlValues, , xlByColumns, xlPrevious).Column).Address
MsgBox x
End Sub
******************************************************************
ou bien encore :
If Not IsError([z]) Then Names("z").Delete
Names.Add Name:="z", RefersTo:="=MAX(IF(A1:H30<>"""",ROW(A1:H30)))"
MsgBox [z]
pour l'adresse de la cellule à l'intersection de la dernière ligne et de la dernière colonne,
If Not IsError([k]) Then Names("k").Delete
ActiveWorkbook.Names.Add Name:="k", RefersTo:= _
"=ADDRESS(MAX(IF(Feuil2!$9:$58<>"""",ROW(Feuil2!$9:$58))),MAX(IF(Feuil2!$9:$58<>"""",COLUMN(Feuil2!$9:$58))))"
MsgBox [k]
*********************************************************************
Et encore :
Sub Evaluate_AV()
MsgBox [address(max(if(Plage<>"",row(plage))),max(if(Plage<>"",if(row(Plage)=max(if(Plage<>"",row(Plage))),column(Plage)))))]
End Sub
Isabelle, Jacques Boisgontier, Denis Michon, Alain Vallon,
Ajouté ou modifié le 17/05/2007 (N°1833)
Ajouté ou modifié le 17/05/2007 (N°1833)
Comment trouver dans une colonne qui contient à la fois des nombres et du texte la dernière valeur en texte ? Et celle contenant des nombres ?
Dernier TEXTE d'une Plage avec cellules vides ou quasivides (les quasivides sont des formules qui produisent des vides):
=RECHERCHE(2;1/NON((Plage="")+ESTNUM(Plage));Plage)
Dernière valeur numérique :
(non matricielle)
=RECHERCHE(9^9;A:A)
PS : Eventuellement, remplacer 9^9 par 9^9^9 voire plus ;-)
=RECHERCHE(2;1/NON((Plage="")+ESTNUM(Plage));Plage)
Dernière valeur numérique :
(non matricielle)
=RECHERCHE(9^9;A:A)
PS : Eventuellement, remplacer 9^9 par 9^9^9 voire plus ;-)
Daniel Maher, Alain Vallon,
Ajouté ou modifié le 02/07/2005 (N°1645)
Ajouté ou modifié le 02/07/2005 (N°1645)
A quoi se réfère "usedrange" et comment peut on le manipuler ?
UsedRange se réfère à la plus petite plage rectangulaire de cellules contenant
toutes les cellules utilisées dans une feuille. Cette plage est par exemple
A1:BC492 si'il n'y a aucune cellule utilisée dans les colonnes à droite de BC et
aucune dans les lignes en dessous de 492.
Tu peux utiliser UsedRange pour tester par exemple toutes les cellules de la
plage :
For each cel in UsedRange
...
Next cel
Si tu veux boucler sur les cellules de la colonne B sans tester
celles qui sont forcément vides tu peux utiliser l'intersection de UsedRange et
de la colonne B :
For Each c In Intersect(UsedRange, Range("b:b"))
c.Value = "fait chaud !"
Next c
ou encore :
For Each Cel in
ActiveSheet.UsedRange.Columns(2).Cells
Next cel
toutes les cellules utilisées dans une feuille. Cette plage est par exemple
A1:BC492 si'il n'y a aucune cellule utilisée dans les colonnes à droite de BC et
aucune dans les lignes en dessous de 492.
Tu peux utiliser UsedRange pour tester par exemple toutes les cellules de la
plage :
For each cel in UsedRange
...
Next cel
Si tu veux boucler sur les cellules de la colonne B sans tester
celles qui sont forcément vides tu peux utiliser l'intersection de UsedRange et
de la colonne B :
For Each c In Intersect(UsedRange, Range("b:b"))
c.Value = "fait chaud !"
Next c
ou encore :
For Each Cel in
ActiveSheet.UsedRange.Columns(2).Cells
Next cel
Flo Cabon, Jacques Chaussard, Michel (msa), (N°19)
Comment sélectionner et nommer un tableau à nombre variable de lignes ?
Tu peux créer une plage nommée dont l'étendue serait déterminée par une
formule à l'aide de la fonction décaler
Définir une plage de cellules
=DECALER(Feuil1!$B$1;0;0;NBVAL(Feuil1!$B:$B);1)
formule à l'aide de la fonction décaler
Définir une plage de cellules
=DECALER(Feuil1!$B$1;0;0;NBVAL(Feuil1!$B:$B);1)
Laurent Longre, (N°18)
J'ai utilisé Cells(lig,col) pour gérer la construction d'un tableau. Comment puis-je faire
pour sélectionner une partie de ce tableau en utilisant des numéros de lignes et de colonnes ?
Range(cells(lig,col),cells(lig,col)).select
Jean-François Beauplet, (N°17)
En A1 de la feuille "sommaire" j'ai le mot "vacances" qui correspond
au nom
d'une feuille. Comment faire référence à la cellule C1 de la feuille "vacances"
en
utilisant la référence A1 de la feuille sommaire ?
1) Si la feuille appartient au même classeur :
=INDIRECT("'"&A1&"'!$1")
où A1 doit contenir le nom de la feuille
et $1 la référence dans la feuille en question
2) Si la feuille appartient à un autre classeur, c'est plus
compliqué :
=INDIRECT("'["&A2&".XLS]"&A1&"'!$1&q
ot;)
où A2 doit contenir le nom du classeur
A1 doit contenir le nom de la feuille
et $1 la référence dans la feuille en question
=INDIRECT("'"&A1&"'!$1")
où A1 doit contenir le nom de la feuille
et $1 la référence dans la feuille en question
2) Si la feuille appartient à un autre classeur, c'est plus
compliqué :
=INDIRECT("'["&A2&".XLS]"&A1&"'!$1&q
ot;)
où A2 doit contenir le nom du classeur
A1 doit contenir le nom de la feuille
et $1 la référence dans la feuille en question
Bernard Mazas,
Ajouté ou modifié le 12/03/2005 (N°16)
Ajouté ou modifié le 12/03/2005 (N°16)
Je fais un somme.si sur des plages d'un autre classeur, ça marche sauf qu'il faut que ce
classeur soit ouvert sinon j'ai #REF en retour. Pourquoi ?
Excel propose deux manières d'afficher les formules comportant des liaisons
vers d'autres classeurs, selon que le classeur source (c'est-à-dire celui qui
fournit des données à une formule) est ouvert ou fermé. Lorsque la
source est ouverte, la liaison contient le nom du classeur entre crochets
droits, suivi du nom de la feuille de calcul, d'un point d'exclamation ( ! )
et des cellules dont dépend la formule. Lorsque la source est fermée, la
liaison contient l'intégralité du chemin d'accès.
=SOMME('C:\Rapports\[Budget.xls]Annuel'!C10:C25)
Le classeur source pour cette formule n'est pas ouvert, ce qui explique
que
la liaison contient le chemin d'accès complet. Si Budget.xls était ouvert,
la formule apparaîtrait sous la forme suivante :
=SOMME([Budget.xls]Annuel!C10:C25).
Remarque Si le nom de l'autre feuille de calcul ou classeur contient des
caractères non alphabétiques, vous devez encadrer le nom (ou le chemin
d'accès) de guillemets dactylographiques simples.
------------------------------------------------
=SOMME('C:\Mes
Documents\Excel\Divers\[Vins_Michel.xls]Fiche Travail'!num)
testé pour la somme de la plage "num" de la feuille "fiche travail" du
fichier "Vins_Michel" du
répertoire "divers" .......fichier fermé. ma
feuille de travail ouverte étant enregistrée.
Tu peux aussi aller voir sur la page fichiers comment récupérer la
valeurs de cellules contenues dans
des classeurs fermés.
vers d'autres classeurs, selon que le classeur source (c'est-à-dire celui qui
fournit des données à une formule) est ouvert ou fermé. Lorsque la
source est ouverte, la liaison contient le nom du classeur entre crochets
droits, suivi du nom de la feuille de calcul, d'un point d'exclamation ( ! )
et des cellules dont dépend la formule. Lorsque la source est fermée, la
liaison contient l'intégralité du chemin d'accès.
=SOMME('C:\Rapports\[Budget.xls]Annuel'!C10:C25)
Le classeur source pour cette formule n'est pas ouvert, ce qui explique
que
la liaison contient le chemin d'accès complet. Si Budget.xls était ouvert,
la formule apparaîtrait sous la forme suivante :
=SOMME([Budget.xls]Annuel!C10:C25).
Remarque Si le nom de l'autre feuille de calcul ou classeur contient des
caractères non alphabétiques, vous devez encadrer le nom (ou le chemin
d'accès) de guillemets dactylographiques simples.
------------------------------------------------
=SOMME('C:\Mes
Documents\Excel\Divers\[Vins_Michel.xls]Fiche Travail'!num)
testé pour la somme de la plage "num" de la feuille "fiche travail" du
fichier "Vins_Michel" du
répertoire "divers" .......fichier fermé. ma
feuille de travail ouverte étant enregistrée.
Tu peux aussi aller voir sur la page fichiers comment récupérer la
valeurs de cellules contenues dans
des classeurs fermés.
Jacques Thiernesse, (N°15)
Je voudrais qu'un message apparaisse lorsque l'utilisateur sélectionne une cellule différente
des cellules prévues. If ActiveCell.Address(0, 0 <> Range("f7") or
ActiveCell.Address(0, 0 <> Range("h7") Then... Ne fonctionne pas !
Tu ne peux pas utiliser <> pour des objets, tu peux l'utiliser pour
des textes (par ex pour l'adresse des plages)
Address est un texte, Range ou ActiveCell est un objet
pour les objets, utilise:
soit
If ActiveCell.Address<> Range("f7").Address and ActiveCell.Address<> _
Range("h7").Address Then...
soit
If Not ActiveCell. Is Range("f7") and Not ActiveCell Is Range("h7") Then
attention aussi au Or / And :
si tu donnes comme condition
If ActiveCell.Address<> Range("f7").Address Or _
ActiveCell.Address<>Range("h7").Address Then
la condition sera TOUJOURS remplie puisque la cellule
active n'a pas don d'ubiquité et ne peut pas être à la fois en F7 et en H7.
Si tu veux que la condition soit remplie si activecell n'est ni sur F7 ni sur h7
tu dois dire AND.
tu aurais aussi pu dire :
If Not(ActiveCell.Address= Range("f7").Address Or ActiveCell.Address= _
Range("h7").Address) Then...
des textes (par ex pour l'adresse des plages)
Address est un texte, Range ou ActiveCell est un objet
pour les objets, utilise:
soit
If ActiveCell.Address<> Range("f7").Address and ActiveCell.Address<> _
Range("h7").Address Then...
soit
If Not ActiveCell. Is Range("f7") and Not ActiveCell Is Range("h7") Then
attention aussi au Or / And :
si tu donnes comme condition
If ActiveCell.Address<> Range("f7").Address Or _
ActiveCell.Address<>Range("h7").Address Then
la condition sera TOUJOURS remplie puisque la cellule
active n'a pas don d'ubiquité et ne peut pas être à la fois en F7 et en H7.
Si tu veux que la condition soit remplie si activecell n'est ni sur F7 ni sur h7
tu dois dire AND.
tu aurais aussi pu dire :
If Not(ActiveCell.Address= Range("f7").Address Or ActiveCell.Address= _
Range("h7").Address) Then...
Jacques Chaussard, (N°14)
Comment écrire l'instruction range("A1:J1").select en utilisant cells (....) ?
Range(Cells(1, 1), Cells(1, 10)).Select
Ou encore
Cells(1,1).Resize(1,10).Select
Ou encore
Cells(1,1).Resize(1,10).Select
ChrisV, (N°13)
J'ai fait une macro qui contient une référence : range("codes!A1"). Si je mets ma
macro dans un module général pas de pb. Si je la mets dans le module d'une feuille la méthode
range de l'objet échoue. Pourquoi ?
Remplace Range("codes!A1") par
sheets("codes").range("A1")
Implicitement, Range("...") correspond à Me.Range("..."),
dans les
modules de feuille. "Me", c'est en l'occurence la feuille de calcul à
laquelle est attaché le module.
Donc si le module est rattaché à la feuille "Feuil1", VBA interprète :
range("codes!A1")
.. comme : Sheets("Feuil1").Range("codes!A1")
D'où l'erreur tout à fait normale que tu as rencontrée sur la méthode
Range.
Il faut donc systématiquement séparer la feuille de l'objet plage:
Sheets("...").Range("...) quand cette plage n'appartient pas à la
feuille "propriétaire" du module.
Ce problème ne se pose pas dans les modules standard, dont le seul
"parent" implicite est l'application.
sheets("codes").range("A1")
Implicitement, Range("...") correspond à Me.Range("..."),
dans les
modules de feuille. "Me", c'est en l'occurence la feuille de calcul à
laquelle est attaché le module.
Donc si le module est rattaché à la feuille "Feuil1", VBA interprète :
range("codes!A1")
.. comme : Sheets("Feuil1").Range("codes!A1")
D'où l'erreur tout à fait normale que tu as rencontrée sur la méthode
Range.
Il faut donc systématiquement séparer la feuille de l'objet plage:
Sheets("...").Range("...) quand cette plage n'appartient pas à la
feuille "propriétaire" du module.
Ce problème ne se pose pas dans les modules standard, dont le seul
"parent" implicite est l'application.
Laurent Longre, (N°12)
Comment récupère-t-on les N° de ligne et de colonne de la cellule active ?
En vba
ligne = activecell.row
colonne = activecell.column
en formule
=LIGNE()
=COLONNE()
ligne = activecell.row
colonne = activecell.column
en formule
=LIGNE()
=COLONNE()
Bruno bdf, (N°11)
Comment connaître la lettre (et non le N°) de la colonne active et le N° de la ligne ?
Colonne = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
Ligne = ActiveCell.Row
Explications (!!)
Pour obtenir la "lettre" de la colonne, il faut extraire soit le premier
caractère de l'adresse si la colonne est avant la colonne 27 (soit les
lettres A-Z), soit les deux premiers caractères si c'est une colonne
plus à droite (AA => IV = deux caractères).
ActiveCell.Address(0, 0) donne l'adresse avec des coordonnées
"relatives" (sans les $). (ActiveCell.Column < 27) renvoie True si la
condition colonne < 27 est vérifiée, False sinon. En ajoutant 2 à cette
valeur, le booléen True/False est tranformé en nombre: -1 si True, 0 si
False.
Donc :
- si colonne < 27, (ActiveCell.Column < 27) + 2 = True + 2 = -1 + 2 = 1
et la fonction Left$ extrait le premier caractère (lettre unique, A à Z)
- si colonne > 26, (ActiveCell.Column < 27) + 2 = False + 2 = 0 + 2 = 2
et Left$ extrait les deux premiers caractères (colonne = 2 lettres, AA à IV)
Quant à la fonction Split, elle fait partie d'un lot d'une dizaine de
nouvelles fonctions de manipulations de chaînes de caractères apportée
par VBA 6 (Excel 2000) et exporte une chaîne comportant des caractères
séparateurs dans une variable tableau. Comme elle n'existe pas sous
Excel 97, je déconseillerais de l'utiliser sauf pour un usage perso.
Ligne = ActiveCell.Row
Explications (!!)
Pour obtenir la "lettre" de la colonne, il faut extraire soit le premier
caractère de l'adresse si la colonne est avant la colonne 27 (soit les
lettres A-Z), soit les deux premiers caractères si c'est une colonne
plus à droite (AA => IV = deux caractères).
ActiveCell.Address(0, 0) donne l'adresse avec des coordonnées
"relatives" (sans les $). (ActiveCell.Column < 27) renvoie True si la
condition colonne < 27 est vérifiée, False sinon. En ajoutant 2 à cette
valeur, le booléen True/False est tranformé en nombre: -1 si True, 0 si
False.
Donc :
- si colonne < 27, (ActiveCell.Column < 27) + 2 = True + 2 = -1 + 2 = 1
et la fonction Left$ extrait le premier caractère (lettre unique, A à Z)
- si colonne > 26, (ActiveCell.Column < 27) + 2 = False + 2 = 0 + 2 = 2
et Left$ extrait les deux premiers caractères (colonne = 2 lettres, AA à IV)
Quant à la fonction Split, elle fait partie d'un lot d'une dizaine de
nouvelles fonctions de manipulations de chaînes de caractères apportée
par VBA 6 (Excel 2000) et exporte une chaîne comportant des caractères
séparateurs dans une variable tableau. Comme elle n'existe pas sous
Excel 97, je déconseillerais de l'utiliser sauf pour un usage perso.
Laurent Longre, (N°10)
Soient 2 plages P1 & P2, P1 est incluse dans P2. Est-il possible à l'aide d'Intersect sous
VBA de déterminer la plage complémentaire, soit la part de P2 non superposée par P1 ?
Avec Plg2 incluse dans Plg1 :
Sub NonIntersect()
For Each Cll In Range("Plg1")
If Intersect(Range(Cll.Address), Range("Plg2")) Is Nothing Then
plg1 = plg1 & Cll.Address & ":" & Cll.Address &
","
If Len(plg1) > 0 Then
Range(Left(plg1, Len(plg1) - 1)).Select
Next Cll
MsgBox Selection.Address
End
Sub
Sub NonIntersect()
For Each Cll In Range("Plg1")
If Intersect(Range(Cll.Address), Range("Plg2")) Is Nothing Then
plg1 = plg1 & Cll.Address & ":" & Cll.Address &
","
If Len(plg1) > 0 Then
Range(Left(plg1, Len(plg1) - 1)).Select
Next Cll
MsgBox Selection.Address
End
Sub
Alain Vallon, (N°9)
Comment faire en utilisant les liens hypertexte pour accéder à une cellule située dans un autre
classeur que la cellule appelante ?
Sous Excel2000, il n'y pas de problème en utilisant le bouton signet de la
boite insérer un lien hypertexte
la syntaxe utilisée est :
c:temptoto.xls#'tata'!b24
ou, par nom à l'intérieur du classeur
c:temptoto.xls#Plage
boite insérer un lien hypertexte
la syntaxe utilisée est :
c:temptoto.xls#'tata'!b24
ou, par nom à l'intérieur du classeur
c:temptoto.xls#Plage
Thierry Rural, (N°8)
Comment trouver la valeur contenue dans la dernière cellule non vide d'une colonne qui peut
contenir des cellules vides ?
FEUILLE :
Si tu nommes Zone (insertion> nom> définir) la région qui t'intéresse:
{=INDIRECT(ADRESSE(MAX((LIGNE(Zone)*(Zone<>"")));COLONNE(Zone)))}
Formule matricielle : ne pas saisir les accolades mais valider par ctrl+maj+entrée
VBA :
ET comment par VBA atteindre la dernière cellule d'une colonne (disons A) ?
range ("A1").end(xldown)
Si tu veux celle qui est juste en dessous pour y inscrire par exemple tes nouvelles données :
range ("A1").end(xldown).offset(0,1).
Si tu a des cellules vides cette méthode te renvoie la première cellule vide en partant du
haut. Si tu as vraiment besoin de la dernière de la colonne, pars du bas :
range ("A65536").end(xlup)
celle du dessous = range("A65536").end(xlup).offset(0,1)
Mais attention ! cette méthode est super dès lors qu'il y a plus d'une cellule remplie dans ta
colonne. Si elle est vide ou si elle ne contient qu'une seule cellule, ceci te renvoie une
erreur. Il faut donc penser à gérer cette situation le cas échéant. Genre (toujours pour
sélectionner la première vide) :
If range ("A1").value= "" then
range("A1").select
Else IF range("A1").value <> "" AND range("A2").value = "" then
range("A2").select
Else
range ("A1").end(xldown).offset(1,0).select
End If
Si tu nommes Zone (insertion> nom> définir) la région qui t'intéresse:
{=INDIRECT(ADRESSE(MAX((LIGNE(Zone)*(Zone<>"")));COLONNE(Zone)))}
Formule matricielle : ne pas saisir les accolades mais valider par ctrl+maj+entrée
VBA :
ET comment par VBA atteindre la dernière cellule d'une colonne (disons A) ?
range ("A1").end(xldown)
Si tu veux celle qui est juste en dessous pour y inscrire par exemple tes nouvelles données :
range ("A1").end(xldown).offset(0,1).
Si tu a des cellules vides cette méthode te renvoie la première cellule vide en partant du
haut. Si tu as vraiment besoin de la dernière de la colonne, pars du bas :
range ("A65536").end(xlup)
celle du dessous = range("A65536").end(xlup).offset(0,1)
Mais attention ! cette méthode est super dès lors qu'il y a plus d'une cellule remplie dans ta
colonne. Si elle est vide ou si elle ne contient qu'une seule cellule, ceci te renvoie une
erreur. Il faut donc penser à gérer cette situation le cas échéant. Genre (toujours pour
sélectionner la première vide) :
If range ("A1").value= "" then
range("A1").select
Else IF range("A1").value <> "" AND range("A2").value = "" then
range("A2").select
Else
range ("A1").end(xldown).offset(1,0).select
End If
Alain Vallon, Flo Cabon,
Ajouté ou modifié le 24/10/2004 (N°7)
Ajouté ou modifié le 24/10/2004 (N°7)
Comment fait-on référence à une cellule contenue dans un autre classeur ?
Ecris =[Test]Feuil1!A1 : la référence externe nom du classeur entre [ ] et le
nom de la
feuille suivi d'un !
Juste une petite précision en cas de saisie manuelle de formule:
si le nom du classeur et/ou le nom de l'onglet possèdent des espaces...
='[classeur]onglet'!réfCellule
nom de la
feuille suivi d'un !
Juste une petite précision en cas de saisie manuelle de formule:
si le nom du classeur et/ou le nom de l'onglet possèdent des espaces...
='[classeur]onglet'!réfCellule
ChrisV, Alain Vallon, (N°6)
J'ai un tableau de 144 colonnes, 96 lignes, sous excel ; j'ai plein de calculs à faire dessus,
et par commodité il serait bien plus facile de les faire :
- en le gérant comme un tableau dans un langage de programmation ( c'est à dire, si le tableau
s'appelle T, si je tape T(i+5,
j-i) je récupere la valeur de la case i+5,j-i ),
- en faisant des boucles. Est ce possible ?
Du gateau ! Remplis par exemple les cellules B3:I20 de valeurs quelconques puis
essaye la procédure ci-dessous :
Sub Tablo()
Dim Tableau, i&, y&
Tableau =
ActiveSheet.Range("B3:I20").Value
For i = LBound(Tableau, 1) To
UBound(Tableau, 1)
For y = LBound(Tableau, 2) To UBound(Tableau, 2)
MsgBox Tableau(i, y)
Next y
Next i
End Sub
Une plage de cellules est un tableau à deux dimensions.
Tu peux initialiser ses dimensions et le remplir
en une seule opération.
Le tableau obtenu est de la forme Tableau(NbLignes,
NbColonnes).(Tout ça pour Excel 2000.
essaye la procédure ci-dessous :
Sub Tablo()
Dim Tableau, i&, y&
Tableau =
ActiveSheet.Range("B3:I20").Value
For i = LBound(Tableau, 1) To
UBound(Tableau, 1)
For y = LBound(Tableau, 2) To UBound(Tableau, 2)
MsgBox Tableau(i, y)
Next y
Next i
End Sub
Une plage de cellules est un tableau à deux dimensions.
Tu peux initialiser ses dimensions et le remplir
en une seule opération.
Le tableau obtenu est de la forme Tableau(NbLignes,
NbColonnes).(Tout ça pour Excel 2000.
Frédéric Sigonneau, (N°5)
Je voudrais afficher en C1 la valeur de la cellule située immédiatement en dessous de la
cellule A1 (mais sans lui dire A2) quelque chose genre (A1+1)
en C1 tu écris =Decaler(a1;1;0)
Ou encore :
=INDIRECT("a" & CELLULE("ligne";A1)+1)
Cette astuce est illustrée dans ces classeurs exemples :
jb-decaler (téléchargé 13749 fois)
jb-graphiquedecaler (téléchargé 5439 fois)
fc-graphdyn (téléchargé 10022 fois)
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 123 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 232
graphdynamique (téléchargé fois)
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 124 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 230
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 125 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 232
(téléchargé fois)
Ou encore :
=INDIRECT("a" & CELLULE("ligne";A1)+1)
Cette astuce est illustrée dans ces classeurs exemples :jb-decaler (téléchargé 13749 fois)
jb-graphiquedecaler (téléchargé 5439 fois)
fc-graphdyn (téléchargé 10022 fois)
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 123 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 232
graphdynamique (téléchargé fois)
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 124 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 230
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 125 in /home/misange/domains/excelabo.net/public_html/include/headexcel.php on line 232
(téléchargé fois)
Pierre Fauconnier, (N°4)
Pour quelle raison, si je remplace
If Target.Address = "$D$4" Then
par « If Target.Address = "D4" Then ...
Cela ne fonctionne plus
Par défaut , Excel donne target.address en référence absolue
Si tu veux lui parler de référence relative il faut mettre :
If Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) = "D4"
Then
ou encore pour ne pas utiliser la référence absolue, il faut remplacer Address
par
Address(0,0)
Address(RowAbsolute:=False) ' $A1
Address(ReferenceStyle:=xlR1C1) ' L1C1
Si tu veux lui parler de référence relative il faut mettre :
If Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) = "D4"
Then
ou encore pour ne pas utiliser la référence absolue, il faut remplacer Address
par
Address(0,0)
Address(RowAbsolute:=False) ' $A1
Address(ReferenceStyle:=xlR1C1) ' L1C1
Isabelle, Pierre Fauconnier, Denis Michon, (N°3)
La macro ci dessous roule sans pb pour le 1° onglet qui est vide mais fait une erreur
d'exécution 1004 avec le 2° ...
For Each onglet In Array("1", "2",
"3")
Sheets(onglet).Range("A1").Select
If Range("A1").Value <> "" Then ....
Où est l'erreur ?
C'est à cause de ta ligne
Sheets(onglet).Range("A1").Select
que VBA s'obstine désespérément à refuser d'exécuter.
Mais en fait elle est inutile. Il te suffit de travailler sur chaque feuille
sans y aller :
For Each onglet In Array("1", "2", "3")
With Sheets(onglet)
If .Range("A1").Value <> "" Then
.Range("A1").CurrentRegion.ClearContents
End If
End With
Next onglet
Ta syntaxe de départ est correcte, mais avant de sélectionner une
plage dans une feuille, il faut activer la feuille.
For Each onglet In Array("1", "2", "3")
Sheets(onglet).Activate
Sheets(onglet).Range("A1").Select
Et on peut quand même le faire en une seule ligne si on veut vraiment
sélectionner, avec
Application.Goto Sheets("Feuil1").Range("A1")
sans avoir activé la feuille auparavant.
Sheets(onglet).Range("A1").Select
que VBA s'obstine désespérément à refuser d'exécuter.
Mais en fait elle est inutile. Il te suffit de travailler sur chaque feuille
sans y aller :
For Each onglet In Array("1", "2", "3")
With Sheets(onglet)
If .Range("A1").Value <> "" Then
.Range("A1").CurrentRegion.ClearContents
End If
End With
Next onglet
Ta syntaxe de départ est correcte, mais avant de sélectionner une
plage dans une feuille, il faut activer la feuille.
For Each onglet In Array("1", "2", "3")
Sheets(onglet).Activate
Sheets(onglet).Range("A1").Select
Et on peut quand même le faire en une seule ligne si on veut vraiment
sélectionner, avec
Application.Goto Sheets("Feuil1").Range("A1")
sans avoir activé la feuille auparavant.
Catherine et Sainte Zaza, Bruno BdF, (N°2)
Comment faire référence à la cellule A2 à partir de l'adresse de la cellule A1 ?
Tu as trois possibilités en VBA :
Range("a1").offset(1,0)
Range("A1").offset(1)
Range("A1")(2)
Range("a1").offset(1,0)
Range("A1").offset(1)
Range("A1")(2)
Pierre Fauconnier, (N°1)