Mise à jour auto des références de cellules dans une macro VBA

Bonjour,

Je débute en VBA et je me pose la question suivante : les références de cellules peuvent-elles se mettre à jour automatiquement dans le code VBA si des lignes ou des colonnes sont insérées ou supprimées dans la feuille correspondante ?

Par exemple :

La cellule A4 de ma feuille contient un 1 et j'ai le code VBA suivant :

Sub Test()

    If Cells(4, 1) = 1 Then
        MsgBox "OK"
    Else
        MsgBox "NOK"
    End If

End Sub

Admettons que je supprime les 2 premières lignes de ma feuille : le 1 se retrouve en cellule A2. Y a-t-il donc un moyen pour que, de manière automatique, le code VBA se mette à jour comme suit :

Sub Test()

    If Cells(2, 1) = 1 Then
        MsgBox "OK"
    Else
        MsgBox "NOK"
    End If

End Sub

Merci d'avance.

Salut pruneau,

en fonction des particularités de fonctionnement de ton fichier, il doit être possible de remédier à toute situation pour peu qu'on l'ait prévue!

Avec ou sans VBA (salut jmd ) d'ailleurs!

Donc, à priori, je dirais oui mais, sans fichier clair et explications complètes, je ne pourrais pas te le garantir non plus!

A+

Bonjour,

Je vais te donner mon avis, sache que certaines personnes sur ce forum ont sûrement d'autres solutions.

Pour moi il faudrait soit que ce soit le résultat d'une recherche Columns(1).find(...) qui te renvoie la ligne, et dans ce cas il faudra une sorte d'étiquette pour trouver toujours cette ligne la parmis toutes les lignes, sinon tu peux également faire une feuille macro où tu stockes des adresses, des lignes, des colonnes, que tu vas chercher au début de ta macro, seulement, cette méthode demande de rajouter une feuille... mais au moins ça suivrait les suppressions et les insertions de ligne.

Merci curulis57 pour ta réponse.

Pour être plus précis, j'ai récupéré un fichier Excel avec beaucoup de macros et des centaines de lignes de codes, que je dois modifier, améliorer, optimiser. Pour ce faire, j'ai besoin d'ajouter ou supprimer des colonnes ou des lignes, renommer des feuilles, etc. Sauf qu'en faisant ça, comme les références des cellules ne se mettent pas à jour automatiquement dans le code, les macros ne fonctionnent plus. Je voulais donc savoir si je suis censé mettre à jour à la main toutes les références, ou s'il existe un moyen plus rapide.

Merci Ausecour pour ta réponse.

J'ai compris les 2 approches, je les garde en mémoire. Mais, comme je le disais à curulis57, les macros sont déjà écrites, donc ça reviendrait à toute reprendre à la main.

Salut pruneau,

si le code VBA est écrit avec des références littérales, tu devras tout repasser manuellement et remplacer idéalement par des variables qui te permettraient alors de perenniser ton code.

Mais, comme tu adaptes un code à tes besoins, ce serait sans doute plus judicieux, après analyse, de réécrire simplement quelques macros.

Encore une fois, sans fichier et explications, difficile de se faire une idée.

A+

Bonjour,

Pour ce que tu demandes et de ce que j'en ai compris, c'est non. VBA ne se comporte pas comme Excel qui, si tu supprimes des cellules dans une colonne corrige les références. Par contre, tu peux utiliser une colonne que tu peux cacher où des cellules font références aux cellules qui risques d'être déplacées.

Un exemple, en B4 tu entres la formule =A4

et tu testes le code ci-dessous puis tu supprimes deux cellules en colonne A (par exemple A1 et A2) et tu re-testes le code :

Sub Test()

    Dim Cel As Range
    Dim Col As Integer
    Dim Lig As Long

    Set Cel = Range("B4")

    Lig = Range(Cel.Formula).Row
    Col = Range(Cel.Formula).Column

    If Cells(Lig, Col).Value = 1 Then MsgBox "OK" Else MsgBox "NOK"

End Sub

Merci Theze pour ta réponse !

À quoi servent Lig et Col ?

Est-ce qu'on ne pourrait pas s'en passer et écrire tout simplement :

If Cel.Value = 1 Then MsgBox "OK" Else MsgBox "NOK"

Pruneau,

à ce rythme-là, Theze sera épuisé sous peu!

Envoie un fichier, explique quelque peu et on pourra avancer par pans plus complets?

A+

D'après moi on peut,

après tout l'instruction set cel = Range("B4") enregistre l'objet cellule B4 dans cel, en appelant cel.value ça revient à appeler Range("B4"), en revanche, je ne vois pas en quoi cela corrige ton problème.

Quand je code en VBA je dois rarement passer par une ligne à l'intérieur du tableau sans un Find, si c'est le cas alors peut-être faut-t-il revoir ton programme

Sinon pour accéder à la fin de ton tableau tu peux plutôt faire :

der_lig = Range("A"&rows.count).end(xlup).row

Tu pourrais nous transmettre une épuration de ton fichier afin qu'on voit à quoi correspond cette ligne 4?

Re,

Tu peux l'écrire sur une seule ligne :

If Cells(Range(Range("B4").Formula).Row, Range(Range("B4").Formula).Column).Value = 1 Then MsgBox "OK" Else MsgBox "NOK"

Mais pour utiliser une variable comme par exemple "Cel", il te faut au préalable l'initialiser avec l'instruction "Set" :

Sub Test()

    Dim Cel As Range

    Set Cel = Cells(Range(Range("B4").Formula).Row, Range(Range("B4").Formula).Column)

    If Cel.Value = 1 Then MsgBox "OK" Else MsgBox "NOK"

End Sub