Modification d'une plage (en VBA uniquement) : références absolues vers mixte

[dss]

Bonjour à tous,

A partir d'une recherche (cell.find) je délimite une plage de cellule à l'aide de la variable Maplage (cellule trouvée, cellule trouvée.end(XLdown). Je nomme ensuite cette plage : range(maplage, maplage.end(XLdown).name ="TOTO
Cette plage, dont je connais l'adresse uniquement par retour Msgbox ou Insertion nom (Excel) est sous la forme : $A$5 : $A$11 (Références absolues).
Est-il possible via VBA de modifier cette plage en référence mixte comme cela : A$5 : A$11 (j'ai absolument besoin de conserver les lignes en l'état pour la hauteur de la plage) ==> la finalité étant un calcul avec NB.SI sur cette plage (qui peut varier au gré des importations de fichier) et pouvoir ainsi décaler le calcul sur les colonnes suivantes à partir du nom "TOTO".

Merci par avance de votre aide

Cordialement

 

[pierrejean]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

bonjour dss

Essaie cette fonction:

Function refmixt(refabs)
ref = Mid(refabs, 2)
x = InStr(ref, ":")
refmixt = Mid(ref, 1, x) & Mid(ref, x + 2)
End Function

 

[Dranreb]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonsoir.

Ce que je fais habituellement: Je donne un nom aux lignes entières, et des noms aux colonnes entières. Pour les paramètres de formules qui doivent consister à coup sûr en une seule cellule, on peut spécifier juste le nom de la colonne, il considère de lui même que vous parlez de son intersection avec la ligne portant la formule. Pour un NB.SI ou autres vous pouvez spécifier le nom des lignes entières et le nom de la colonne concernée séparés par un espace. Celui ci fait en effet office d'opérateur d'intersection. Celui ci permet aussi de se sortir de cas tordus où on veut spécifier une seule cellule alors qu'il peut en être spécifié plusieurs.
Si vous ne donnez pas de noms aux colonnes, tout ce que j'ai dit à leur sujet s'applique aussi aux adresses de colonnes:
Si Tablo = $2:$100 alors Tablo $A:$A = $A$2:$A$100

 

[dss]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Ré,
Grand merci à tous deux de votre aide précieuse. Je regarde tout ça demain et ne manquerai pas de vous tenir informé.
Bonne soirée

Bien cordialement
DSS

 

[dss]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonjour PierreJean et Dranreb, bonjour à tous,

Je reviens vers vous après avoir m'être acharné sur la fonction de Pierre Jean : incontestablement, elle fonctionne et cela semble répondre parfaitement à mon besoin. Le problème, c'est que je ne suis pas à l'aise avec le maniement des fonctions (s'il n'y avait que ça... ) et je n'arrive pas à l'affecter dans la procédure sub.
Je joins un extrait du fichier concerné avec la macro, je pense que cela plus aisé si vous souhaitez me dépanner.
Merci encore
Bien cordialement
DSS

 

[pierrejean]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Re

Sniff !!
Je n'ai pas réussi à placer ma superbe fonction dans ta macro
Par contre je pense l'avoir fait fonctionner
Veux-tu verifier S.T.P

 

[dss]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Re
Tout d'abord, merci à nouveau de ta contribution. J'ai essayé ta superbe fonction (fonctions personnalisées-->Refmixt) sur mon poste perso (macbook pro avec excel 2011 pour Mac). Celui au travail fonctionne sous windows XP excel 2002 : j'ai quelquefois des petits écarts entre les deux.
Pour le test, refmixt(INORDA) ou REFMIXT(B5:B11) me renvoie #VALEUR! Et si je copie refmixt(INORDA) de "B vers "C", plage sélectionnée colonne B et non C.
Mais c'est sur mac, alors...
A ton avis, les références absolues attribuées par VBA sont-elles irréversibles ?
Je profite aussi de cet échange pour saluer Dranreb et le remercier également : mais sa solution m'apparaît inaccessible vu mes compétences actuelles en VBA...
Pour l'heure et selon ta réponse,PierreJean, je peux éventuellement me préparer à pratiquer "l'offset" de plage en plage et l'insertion de nom :au bout du compte, çà au moins, je devrais savoir le faire...
Bien cordialement et merci à vous deux
DSS

 

[eriiic]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonjour,

autre possibilité :

ref = "$A$5:$A$11"
ref = Range(ref).Address(ColumnAbsolute:=False)

Supporte également bien ref = "A5:A11"
eric

 

[laurent950]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonsoir,

' Absolut = saisie $A$1:$A$11
MsgBox maplage1.Address 'vŽrification de la plage d'adresse
' Relatif = VBA A$1:A$11
MsgBox maplage1.Address(1, 0) 'vŽrification de la plage d'adresse

laurent

 

[dss]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonsoir Eric et Laurent, le forum

Ré,
Eric, merci pour votre réponse. J'ai essayé la solution que vous proposez mais malheureusement je n'ai obtenu aucun résultat malgré plusieurs combinaisons. En la laissant telle quelle, puis en remplaçant ref par maplage1 (variable de ma macro). Egalement en remplaçant $A$5:$A$11 par maplage1 (solution qui m'intéresse) puisque c'est maplage1 qui est définie par :

Set maplage1 = Range(n1.Offset(1, 0), n1.Offset(1, 0).End(xlDown))
Range(maplage1, maplage1.End(xlDown)).Name = "TOTO"

Maplage1 dépend du résultat de la recherche avec cell.find, résultat affecté à la variable n1 : la plage peut donc varier sur sa hauteur et je ne la connais pas à l'avance: ce pourra être autre chose que $A$5: $A$11 en fonction des données importées en amont. Il y a d'ailleurs plusieurs plages successives définies comme ci-dessus dont je ne connais ni le début ni la fin à priori, mais définie et nommée à la suite de ".End(xlDown)".

Il se peut que je me trompe quelque part (syntaxe ou autre), avez-vous une idée?

Cordialement

DSS

 

[pierrejean]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Re

Pourquoi vouloir aussi obstinément nommer tes plages et chercher leurs adresses à partir de ces noms ???

 

[Dranreb]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonjour.
C'est vrai quoi ! Je ne comprends toujours pas non plus pourquoi vous ne faites pas plutôt Range(MaPlage, MaPlage.End(xlDown)).EntireRow.Name ="TOTO"
Ce serait tellement plus simple ensuite à utiliser pour n'importe quelle colonne !

 

[eriiic]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonjour,

Cette plage, dont je connais l'adresse uniquement par retour Msgbox

Je pensais que tu avais ta référence en texte.

Je ne vois pas pourquoi tu ne peux utiliser la proposition de dranreb.
Ex de formule : =SOMME(B:B TOTO) fera la somme de B pour les lignes définies dans TOTO

Et si tu tiens absolument à ta référence mixte :

Names.Add Name:="toto", RefersTo:="=" & maplage1.Parent.Name & "!" & maplage1.Address(, 0)

ou bien directement à partir de n1 :

Names.Add Name:="toto", RefersTo:="=" & n1.Parent.Name & "!A$" & n1.Row + 1 & ":A$" & n1.Offset(1, 0).End(xlDown).Row

eric

 

[dss]

Re : Modification d'une plage (en VBA uniquement) : références absolues vers mixte

Bonjour,

La solution effectivement m'avait été donnée... A un détail près, je ne savais pas que dans NB.SI, on pouvait faire sur la plage une sélection de la colonne associée à un nom. Je comprends d'autant mieux votre étonnement et votre insistance devant cette situation évidente pour vous...(mais par pour moi).
J'ai finalement suivi à la lettre les conseils de Dranreb et découvert le "B:B TOTO" sur NB.SI avec Eric, et là ça fonctionne nickel
Une petite question sans vouloir abuser : est ce que la combinaison sur la plage sélectionnée (colonne, nom) est opérante sur la version excel 2002, je ne peux actuellement la tester que sur mac excel 2011 ?
Je crois enfin avoir compris les propos de Dranreb (à l'aide de l'exemple d'Eric) : nommer lignes et colonnes qui devait se traduire par Entirerow & Entirecolumn et trouver l'intersection commune de chacune avec Intersect !!!
Pour tout dire, j'ai essayé de nommer les colonnes de la même façon que les lignes puis vérifier leur intersection avec intersect. Mais curieusement msgbox me répond " pas d'intersection " : sans doute encore une subtilité mais je ne désespère pas de trouver un jour la solution...
Enfin et surtout, grand merci à vous tous pour votre soutien et votre aide qui me permettent désormais de pouvoir poursuivre mon projet (laborieux) plus en avant.
Bien cordialement
DSS