Utiliser le résultat de last row pour changer une formule via une macro

Version imprimable

Bonjour à tous.
Débutant en Vba j'ai quelques notions et j'arrive en général à me débrouiller mais là je tombe sur un os.
En effet je souhaite créer une macro qui change une valeur dans des formules en fonction du nombre de lignes utilisées.
J'arrive à déterminer le nombre de ligne et aussi à faire une macro pour modifier mes formules mais impossible de lier les deux.
En effet je n'arrive pas à " faire rentrer " le chiffre du nombre de ligne dans la macro.
Si quelqu'un a la solution merci beaucoup par avance.
Voici ce que j'ai programmé :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub formule() ' ' macro pour modifier les formules de chaque feuille ' ' Dim Lr As Long Lr = Sheets("HEURE_MAT").Cells(Rows.Count, 3).End(xlUp).Row Dim f As Variant For Each f In Array("S 00", "S 01", "S 02", "S 03", "S 04", "S 05", "S 06", "S 07", "S 08", "S 09", "S 10", "S 11", "S 12", "S 13") Sheets(f).Activate Cells.Replace What:="SAIQ!$A$1:$D$138", Replacement:="SAIQ!$A$1:$D$Lr", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Next Sheets("HEURE_MAT").Activate End Sub
Concrètement je veux remplacer 138 par le nombre de lignes non vides Lr.
Je travaille avec Excel 2010.
Pas simple pour moi...🙃

Bonjour,

Essayez ceci (Attention, non testé)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub formule()
    Dim Lr As Long
    Dim f As Variant
    Dim f1 As Worksheet, f2 As Worksheet
    Set f1 = Sheets("HEURE_MAT")
    Lr = f1.Cells(Rows.Count, 3).End(xlUp).Row
    For Each f In Array("S 00", "S 01", "S 02", "S 03", "S 04", "S 05", "S 06", "S 07", "S 08", "S 09", "S 10", "S 11", "S 12", "S 13")
        Set f2 = Sheets(f)
        f2.Cells.Replace What:="SAIQ!$A$1:$D$" & Lr, Replacement:="SAIQ!$A$1:$D$" & Lr, LookAt:=xlWhole
    Next
    f1.Activate
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Question: Pourquoi ne pas réécrire la formule directement avec l'aide de VBA?

Cdlt

01/04/2021, 08h07
Cyracless

Bonjour arturo 83.

Tout d'abord merci d'avoir pris du temps pour mon petit problème excel.
Je vais tester votre macro dès que possible ( certainement ce we).
Pour répondre à votre question j'ai 140 fois la formule fois 13 feuilles et surtout le besoin de changer cette variable du nombre de ligne pour chaque utilisation du fichier.
Après je suis ouvert à toute proposition si c'est plus simple.
Bien cordialement.
01/04/2021, 08h37
ARTURO83

Pourriez-vous indiquer la formule que vous utilisez?
07/04/2021, 11h42
Pierre Fauconnier

Salut.

Perso, j'essaierais d'abord de me débrouiller en Excel avant de penser à du VBA. Une piste pourrait être d'utiliser des "plages nommées", voire une formule qui renverrait le nombre de lignes.

Détailler et expliquer ton besoin nous aiderait à t'aider.

Ps: Perso, j'éviterais le hardcoding (mettre des données dans le code VBA, telles que le tableau Array("S 00", "S 01", "S 02", "S 03", "S 04", "S 05", "S 06", "S 07", "S 08", "S 09", "S 10", "S 11", "S 12", "S 13")), ça ne peut qu'amener des ennuis et lourdeurs lors d'une modification de ces valeurs. Le hardcoding est vraiment une pratique à bannir!

Bonsoir,

J'ai essayé votre macro mais cela ne fonctionnait pas. Mais je m'en suis inspiré pour récrire un peu la mienne sous cette forme :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub formule()
 
Dim Lr As Long
 
Lr = Sheets("HEURE_MAT").Cells(Rows.Count, 3).End(xlUp).Row
 
Dim f As Variant
 
For Each f In Array("S 00", "S 01", "S 02", "S 03", "S 04", "S 05", "S 06", "S 07", "S 08", "S 09", "S 10", "S 11", "S 12", "S 13")
 
    Sheets(f).Activate
 
    Cells.Replace What:="SAIQ!$A$1:$D$138", Replacement:="SAIQ!$A$1:$D$" & Lr, _
 
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
Sheets("HEURE_MAT").Activate
 
End Sub

Et ça fonctionne.

Un grand merci à vous je n'y serai pas arrivé seul.

08/04/2021, 07h52
Pierre Fauconnier

Je répète encore une fois que, a priori, un tableau structuré sur la feuille Heure_Mat permettrait de se passer de macros pour déterminer la plage.

De plus, cette macro ne peut être qu'un "one shot" car lorsque tu auras remplacé "SAIQ!$A$1:$D$138" par "SAIQ!$A$1:$D$XXX", ton code ne fonctionnera plus puisqu'il ne pourra plus chercher "SAIQ!$A$1:$D$138" mais devra chercher "SAIQ!$A$1:$D$XXX"...

Mais bon, chacun suit les conseils qu'il veut et se complique la vie s'il le souhaite. Bon courage avec ton usine à gaz, car tu n'as pas fini de suer ;)