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

Marcham · 26 Août 2023

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 · 26 Août 2023

Bonjour @Marcham

J'ai pas vu ton fichier !!!!

Gégé-45550 · 26 Août 2023

Marcham à dit:
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 :

VB:

=INDIRECT(A2 & "!" & B2)

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

mapomme · 26 Août 2023

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 · 26 Août 2023

Phil69970 à dit:
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 · 26 Août 2023

Gégé-45550 à dit:
Bonjour,
en C2, sans VBA :

VB:

=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 · 26 Août 2023

mapomme à dit:
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 · 26 Août 2023

Bonjour Marcham, le forum,

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

VB:

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 · 26 Août 2023

Marcham à dit:
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 · 26 Août 2023

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

Voyez plutôt mon post #8.

mapomme · 26 Août 2023

job75 à dit:
Voyez plutôt mon post #8.

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

job75 · 26 Août 2023

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 :

VB:

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 · 27 Août 2023

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 · 27 Août 2023

Bonjour Marcham, le forum,

Allez une petite dernière :

VB:

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+

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

Marcham

XLDnaute Junior

Phil69970

XLDnaute Barbatruc

Gégé-45550

XLDnaute Accro

mapomme

XLDnaute Barbatruc

Marcham

XLDnaute Junior

Marcham

XLDnaute Junior

Marcham

XLDnaute Junior

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Marcham

XLDnaute Junior

job75

XLDnaute Barbatruc

Discussions similaires