Traduire INDIRECT dans VBA

Bonjour
je souhaiterais traduire la fonction INDIRECT en code VBA.
Dans la colonne A3:A de la feuille nommée Liste je récupère le nom de toutes les autres feuilles (environ 300)
La ligne (plage) B2:BB contient la référence des cellules à renvoyer.
j'ai la fonction =INDIRECT("'"&$A3&"'!"&B$2) aux intersections colonne-ligne.
lors de nouvelles feuilles ou de valeurs à renvoyer il faut glisser la formule d'où les risques d'erreurs

j'ai essayé ce code je bloque sur la deuxième partie
merci

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Sub liste() 'Récupération du nom des feuilles dans la colonne A Dim ws As Worksheet Set ws = Worksheets("Liste") Dim nb As Byte nb = Worksheets.Count - 1 Dim last_row As Long last_row = ws.Range("A65000").End(xlUp).Row ws.Range("A2:A" & last_row).Clear For i = 9 To nb On Error GoTo 0 ws.Cells(i - 6, 1) = Worksheets(i + 1).Name Next i 'Récupération des valeurs. comme INDIRECT For j = 2 To 40 ws.Cells(j, 3).Value = sheets("'"&a3&"'"!"&a12"").value Next End Sub

---

Bonjour counterbob,

Je ne vois pas trop où est ton problème... Ta formule fonctionne très bien, même quand tu la tires.

Pourquoi vouloir passer par VBA ?

Peux-tu nous en dire plus stp ?

Bonjour,
ce classeur est en réseau et les utilisateurs ne connaissent pas vraiment les fonctions.
et lorsque l'on veut récupérer des données en rajoutant une référence de cellule dans la ligne 2 on oublie de tirer la formule.
de plus le classeur fait 3Mo et je pensais que limiter les valeurs des cellules limiterais la taille du classeur.
environ 6000 cellules contiennent la formule.

merci

J'ai réalisé ce code , ça fonctionne bien, un peu lent à mettre à jour mais ça va

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

Sub liste()   'Récupération des noms de feuilles Dim ws As Worksheet Set ws = Worksheets("Liste")   Dim nb As Byte nb = Worksheets.Count - 1   Dim last_row As Long last_row = ws.Range("A65000").End(xlUp).Row   ws.Range("A2:A" & last_row).Clear   For i = 9 To nb On Error GoTo 0 ws.Cells(i - 6, 1) = Worksheets(i + 1).Name   Next i     'Création de la formule et copie dans la plage B3:BB100 Range("B3").Select ActiveCell.FormulaR1C1 = "=IF(ISERROR(INDIRECT(""'""&RC1&""'!""&R2C)),"""",INDIRECT(""'""&RC1&""'!""&R2C))" Range("B3").Select selection.AutoFill Destination:=Range("B3:bb3"), Type:=xlFillDefault Range("B3:BB3").Select selection.AutoFill Destination:=Range("B3:bb100"), Type:=xlFillDefault Range("B3:BB100").Select ActiveWindow.SmallScroll Down:=-93 Range("B3").Select   End Sub

---

Bonjour,
C'est lent notamment parce-que tu utilises la méthode Select et la propriété Selection
La méthode AutoFill n'est pas indispensable, tu peux parfaitement écrire une formule directement dans plusieurs cellules.
Exemple

Code:

---

1 2 3

With ThisWorkbook.Worksheets("db") .Range("S2:S10000").Formula = "=M2*L2" End With

---

Une autre raison de sa lenteur peut provenir de la fonction INDIRECT elle-même qui est une formule volatile. Cela signifie qu'elle se recalcule chaque fois que l'on modifie une valeur quelconque, de n'importe quelle feuille de n'importe quel classeur ouvert.

eh oui Philippe, que je salue, j'avais préparé ce code, à tester

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

..... For i = 9 To nb On Error GoTo 0 'ça c'est ton problème, pour moi pas d'intérêt ws.Cells(i - 6, 1) = Worksheets(i + 1).Name   Next i     'Création de la formule et copie dans la plage B3:BB100   Range("B3:BB100").FormulaR1C1 = "=IF(ISERROR(INDIRECT(""'""&RC1&""'!""&R2C)),"""",INDIRECT(""'""&RC1&""'!""&R2C))" 'ci-dessous,pour faire quoi ? mais c'est ton problème ActiveWindow.SmallScroll Down:=-93 Range("B3").Select .....

---

J'ai réduit le code
effectivement c'est plus clair, la mise à jour prend 10s
merci Philippe, merci Dom