Discussion :

[halaster08]

Bonjour a tous,

Sur un fichier qui est mis à jour trimestriellement j'ai un tableau qui historise une partie de mes données.
A chaque MàJ, via une macro j'insère une nouvelle colonne pour y historiser les derniers résultats avant de les remplacer.
Il y a également à gauche de ce tableau un colonne contenant des formules pour vérifier la cohérence des derniers résultat.
Mon problème est que lors de l'insertion de la nouvelle colonne, l'indice dans les formules de contrôle se décale automatiquement et donc ma formule ne pointe plus sur les derniers résultats mais sur les avant derniers.
Ma question est donc la suivante, est-il possible d'inserer une colonne sans décaler les indices des formules?
Si non, une idée pour contourner le problème?

petit schéma au cas où mes propos ne soient pas clairs

colonne A: formule avec référence à la colonne B
colonne B: valeur
MàJ: insertion de colonne en B
colonne B -> colonne C
colonne A avec référence à la colonne C alors que j'aimerais que la référence reste sur la colonne B

Merci d'avance.

[thebenoit59]

Bonjour Halaster.
Si tu n'as besoin que de la valeur de la cellule en colonne B pour le reste de ta formule, tu peux utiliser la fonction indirect.
Exemple en [a1] = =INDIRECT("b1")
Quant tu vas insérer ta colonne cela restera conforme à ta demande.

[cerede2000]

+1 L'indirection me semble bien approprié

[antonysansh]

Bonjour,

Autre solution :
En A1 :
=DECALER(A1;0;1) fera référence à la cellule juste à droite de la cellule A1. Donc la cellule B1 avant et après insertion en colonne B.

Lien pour plus d'infos sur la fonction DECALER

[halaster08]

Merci à tous,

En fait je cherchais un truc du style

Code :

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

Application.Calculation = xlCalculationManual

A mettre dans ma macro mais pour les décalage d'indices, c'est pour ça que j'avais poster dans la section vba.

Le problème avec vos solutions c'est que dois changer toutes mes formules et y en beaucoup et j'aurais aimé l'éviter mais si j'ai pas le choix...

Du coup c'est la formule d'antony que je vais utiliser car elle s'adapte toute seule au changement de ligne, ce que ne fait pas la formule avec indirect.

Merci encore.

[antonysansh]

Beaucoup de formules, c'est-à-dire ?

T'es formules sont uniquement des formules simples de ce genre : =B1 ?

Si c'est le cas voila une conversion automatique :

En B4 j'ai la formule =D6

Je lance cette procédure :

Code :

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

1
2
3
Sub test()
    Call Convert(Range("B4"))
End Sub

avec

Code :

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

1
2
3
4
5
6
7
8
9
Sub Convert(ByVal rg As Range)
    Dim ref As Range, RowDecalage&, ColumnDecalage&
        Set ref = Range(Replace(rg.Formula, "=", ""))
        RowDecalage = ref.Row - rg.Row
        ColumnDecalage = ref.Column - rg.Column
        rg.Formula = "=OFFSET(" & Replace(rg.Address, "$", "") & "," & RowDecalage & "," & ColumnDecalage & ")"
fin:
    Set ref = Nothing
End Sub

Je me retrouve avec la formule =DECALER(B4;2;2) en B4.

Tu peux ensuite boucler pour faire sur tout ton fichier.

[halaster08]

C'est du genre b1-c1
Et il y en a environ 11*6*5 soit 330, tableau sur 11ans 5 feuilles similaires et 6 tableaux par feuille.

Mais avec ta formule je gagne déjà le *11 sur et peut être le *6 aussi.

Bref rien d'insurmontable heureusement.
Et même si j'avais du refaire les 330 forules je l'aurais fait aussi ou je l'aurais fais en programmant des boucles ce qui débugage compris m'aurais pris plus de temps.

Edit: merci antony pour la macro, j'essayerais de l'adapter pour ma formule, ça me permettra de progresser.

[thebenoit59]

A ce moment là tu peux réagir différemment.
Si nous restons en VBA pur tu peux utiliser ceci :

Code :

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

1
2
3
r = Range("a1:a" & [a65000].End(xlUp).Row).Formula
Cells(1, 2).EntireColumn.Insert
Cells(1, 1).Resize(UBound(r)) = r