XL 2019 VBA Valeur d'une cellule dont la feuille et la ligne est dans la feuille courante

[Marcham]

Bonjour les penseurs du week-end

L'onglet de ma feuille courante s'appelle : ici
La colonne A de cette feuille contient les noms d'onglet différentes feuilles (autre que "ici")
La colonne B de cette même feuille contient des numéros de ligne.

Je souhaiterai que la colonne C, D récupère la valeur contenu dans les colonnes A et B de la feuille et de la ligne indiquée à sa gauche.

Exemple, ma feuille courante
Onglet------Ligne-------------------Je voudrais les valeurs de
A1=labas - B1=12 - C1=Sheets("labas").range("A:12") - D1=Sheets("labas").range("B:12") etc...
A2=labas - B2=45 - C2=Sheets("labas").range("A:45") - D2=Sheets("labas").range("B:45") etc...
A3=ailleurs - B3=9 - C3=Sheets("ailleurs").range("A:9") - D3=Sheets("ailleurs").range("B:9") etc...

Les formats des colonnes A et B de ma feuille courante (ici) sont standards
Les formats des colonnes A et B des autres feuilles sont string date ou integer mais identiques dans chaque colonne

Voici ce que j'ai écrit (et qui ne marche pas)
For I = 1 to 3 (à la fin)
- Range("C" & i) = Sheets(Range("A" & i).Range("A" & i))
- Range("D" & i) = Sheets(Range("A" & i).Range("B" & i))
- Range("E" & i) = Sheets(Range("A" & i).Range("C" & i))
Next i
J'ai l'erreur : Incompatibilité de type, c'est peut-être un problème de déclaration ????
Je vous remercie d'avance - Marc

 

[Phil69970]

Bonjour @Marcham

J'ai pas vu ton fichier !!!!

 

[Gégé-45550]

Bonjour les penseurs du week-end

L'onglet de ma feuille courante s'appelle : ici
La colonne A de cette feuille contient les noms d'onglet différentes feuilles (autre que "ici")
La colonne B de cette même feuille contient des numéros de ligne.

Je souhaiterai que la colonne C, D récupère la valeur contenu dans les colonnes A et B de la feuille et de la ligne indiquée à sa gauche.

Exemple, ma feuille courante
Onglet------Ligne-------------------Je voudrais les valeurs de
A1=labas - B1=12 - C1=Sheets("labas").range("A:12") - D1=Sheets("labas").range("B:12") etc...
A2=labas - B2=45 - C2=Sheets("labas").range("A:45") - D2=Sheets("labas").range("B:45") etc...
A3=ailleurs - B3=9 - C3=Sheets("ailleurs").range("A:9") - D3=Sheets("ailleurs").range("B:9") etc...

Les formats des colonnes A et B de ma feuille courante (ici) sont standards
Les formats des colonnes A et B des autres feuilles sont string date ou integer mais identiques dans chaque colonne

Voici ce que j'ai écrit (et qui ne marche pas)
For I = 1 to 3 (à la fin)
- Range("C" & i) = Sheets(Range("A" & i).Range("A" & i))
- Range("D" & i) = Sheets(Range("A" & i).Range("B" & i))
- Range("E" & i) = Sheets(Range("A" & i).Range("C" & i))
Next i
J'ai l'erreur : Incompatibilité de type, c'est peut-être un problème de déclaration ????
Je vous remercie d'avance - Marc

Bonjour,
en C2, sans VBA :

=INDIRECT(A2 & "!" & B2)

sinon, il n'est pas compliqué de convertir cette formule en VBA.
Cordialement,

 

[mapomme]

Bonjour Marcham ,

A la première lecture et sans aller plus loin, il me semble déjà qu'il y a un problème de parenthèse mal placée dans l'expression Sheets(Range("A" & i).Range("A" & i)), non ?

 

[Marcham]

Bonjour @Marcham

J'ai pas vu ton fichier !!!!

Bonjour, ne t'inquiète pas, c'est tout à fait normal car je ne l'ai pas mis.
Ce fichier est énorme et il faudrait que j'en fasse un plus proche de ce que j'attends.
Je ferais peut-être ça ce soir ou demain si je n'avance pas.
Merci

 

[Marcham]

Bonjour,
en C2, sans VBA :

=INDIRECT(A2 & "!" & B2)

sinon, il n'est pas compliqué de convertir cette formule en VBA.
Cordialement,

Bonjour, mon problème est justement pour le mettre en VBA
Merci

 

[Marcham]

Bonjour Marcham ,

A la première lecture et sans aller plus loin, il me semble déjà qu'il y a un problème de parenthèse mal placée dans l'expression Sheets(Range("A" & i).Range("A" & i)), non ?

Je ne vois pas de problème de parenthèses.
Peut-être des manquements de Sheets mais je n'y arrive pas.
Merci

 

[job75]

Bonjour Marcham, le forum,

Testez sur la feuille courante (celle où les colonnes A et B sont renseignées) :

Sub Test()
Dim i&
With [A1].CurrentRegion
    For i = 1 To .Rows.Count
        .Range("C" & i) = Sheets(.Range("A" & i).Value).Range("A" & .Range("B" & i))
        .Range("D" & i) = Sheets(.Range("A" & i).Value).Range("B" & .Range("B" & i))
        .Range("E" & i) = Sheets(.Range("A" & i).Value).Range("C" & .Range("B" & i))
    Next i
End With
End Sub

A+

 

[mapomme]

Je ne vois pas de problème de parenthèses.

Et pourtant il y en a un !

Quand vous utilisez Sheets(nomMaFeuille), il faut refermer la parenthèse.
Dans votre cas, nomMaFeuille vaut Range("A" & i)
Il faut donc écrire : Sheets(Range("A" & i)) => deux parenthèses fermantes
et supprimer la parenthèse finale qui est en trop (ou autrement dit mal placée).

On arrive donc à : Sheets(Range("A" & i)).Range("A" & i)

Malgré tout, cela ne fonctionnera pas. Car si vous laissez simplement Sheets(Range("A" & i)),
VBA s'attend à recevoir en argument un nombre pour Sheets (argument qui pour lui est le numéro de la feuille ou Index).
il va essayer de convertir le nom de la feuille en nombre. Comme le nom est un texte, il va le convertir en zéro. Or zéro n'est pas un index de feuille (les index commencent à un).

Pour le forcer à considérer le nom de la feuille, on écrira Range("A" & i).Value (et c'est d'ailleurs ce qu'a fait @job75 que je salue ici ). Il faut avouer que c’est assez subtil et tordu.

On arrive donc à : Sheets(Range("A" & i)).Range("A" & i).Value

 

[job75]

On arrive donc à : Sheets(Range("A" & i)).Range("A" & i).Value

Voyez plutôt mon post #8.

 

[mapomme]

Voyez plutôt mon post #8.

Je traitais juste le PB de parenthèse pas une solution.

 

[job75]

Pour tester j'ai recopié la plage A1:B3 sur 30 000 lignes.

La macro précédente s'exécute en 6,3 secondes chez moi.

Pour aller plus vite on utilisera un tableau VBA, cette macro s'exécute en 1,2 seconde :

Sub Test2()
Dim t, tablo, i&
t = Timer
With [A1].CurrentRegion
    tablo = .Resize(, 5)
    For i = 1 To UBound(tablo)
        With Sheets(tablo(i, 1))
            tablo(i, 3) = .Range("A" & tablo(i, 2))
            tablo(i, 4) = .Range("B" & tablo(i, 2))
            tablo(i, 5) = .Range("C" & tablo(i, 2))
        End With
    Next i
    .Resize(, 5) = tablo
End With
MsgBox Timer - t
End Sub

 

[Marcham]

Bonjour, j'ai mis en œuvre la proposition et, naturellement, cela fonctionne.

Mon erreur était liée au défaut de "Value"

Par contre, je suis épaté par la profondeur de vos investissements. Autant Job75 tu vas jusqu'à essayer différentes solutions (avec un tableau) autant Mapomme explique le rôle des parenthèses et le comportement de Sheets.

Ça, c'est de la réponse, chapeau bas !

Je vous remercie tous - Marc

 

[job75]

Bonjour Marcham, le forum,

Allez une petite dernière :

Sub Test2()
Dim t, tablo, i&, a
t = Timer
With [A1].CurrentRegion
    tablo = .Resize(, 5) 'matrice, plus rapide
    For i = 1 To UBound(tablo)
        a = Sheets(tablo(i, 1)).Range("A" & tablo(i, 2)).Resize(, 3) 'matrice, plus rapide
        tablo(i, 3) = a(1, 1)
        tablo(i, 4) = a(1, 2)
        tablo(i, 5) = a(1, 3)
    Next i
    .Resize(, 5) = tablo
End With
MsgBox Timer - t
End Sub

Sur 30 000 lignes => 0,65 seconde.

A+