Discussion :

[counterbob]

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 :

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

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

[mat955]

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 ?

[counterbob]

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 :

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

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

[Philippe Tulliez]

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 :

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

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.

[casefayere]

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

Code :

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

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
.....

[counterbob]

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