XL 2016 vba copier coller grace à adresse

douguy

XLDnaute Junior
Bonjour le forum!

J'ai une question simple en VBA :
je veux créer une macro qui doit copier une plage nommée et la coller dans un onglet "data" en valeur.
Pour choisir où la coller je voudrais définir la cellule de départ grâce à cette formule =ADRESSE(EQUIV(B20;data!A1:A23;0);4)
mon problème est : je ne sais pas comment formuler cela en VBA.

Est ce que quelqu'un aurait une solution?

Bonne journée et un grand merci d'avance.
 

Fichiers joints

Roblochon

XLDnaute Accro
bonsoir,

Avec l'exemple de 'mix'!B30 :
Deux façons parmi d'autres, d'afficher une adresse de cellule dans la fenêtre d'éxécution (CTRL+G)

VB:
Sub truc()
    Dim idx As Variant
    With Sheets("data")
        '
        ' Utilisation de l'équivalent de la fonction EQUIV
        idx = Application.Match(Sheets("mix").Range("B30"), .Range("A1:A23"), 0)
        '
        ' Si Trouvé
        If Not IsError(idx) Then Debug.Print .Cells(idx, 4).Address
    End With
End Sub

Sub chose()
' Sans test d'erreur
    With Sheets("data")
        Debug.Print .Cells(Application.Match(Sheets("mix").Range("B30"), .Range("A1:A23"), 0), 4).Address
    End With
End Sub
bonne programation
 

douguy

XLDnaute Junior
ca marche nickel
merci roblochon! j'ai utiliser le premier code

si je peux abuser : je veux selectionner une plage dans le range N20:R50
N20 est invariant mais pas R50 ( ca peut etre R49 R48 ou R47 en fonction du nb de jour dans un mois)

en piochant à droite à gauche j'arrive a un truc pas mal mais les deux lignes du code en gras bloquent l'execution.
quelle syntaxe dois-je utiliser ?

merci


Sub chose()
' Sans test d'erreur

Dim idx As Variant
Dim plage As Range, C As Range, D As Range

Set plage = Range("n20:r50")
If plage.Cells(plage.Cells.Count) <> "" Then
C = plage.Cells(plage.Cells.Count)
D = C.Adress
Else
Set C = plage.Find(what:="*", After:=plage.Cells(plage.Cells.Count - 1), SearchDirection:=xlPrevious)
End If


With Sheets("mix")
Range("N20: D").Copy
End With

With Sheets("data")
.Activate
'
' Utilisation de l'équivalent de la fonction EQUIV
idx = Application.Match(Sheets("mix").Range("B20"), .Range("A1:A1464"), 0)
'
' Si Trouvé
If Not IsError(idx) Then .Cells(idx, 106).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
End With
End Sub
 

Staple1600

XLDnaute Barbatruc
Bonsoir le fil, douguy, Roblochon

douguy
Ca marchera mieux ainsi
D = C.Address(0,0)
et aussi avec ceci
Range("N20:" & D).Copy
 

Roblochon

XLDnaute Accro
Bonjour,

Comme l'ami @Staple1600 et en plus:

Votre variable C est une variable objet (Range). Il faut donc l'initialiser avec Set: Set C = plage.Cells(plage.Cells.Count)
Votre variable D est déclarer comme objet Range mais vous lui attribuer une valeur String (C.Address). Cela ne peut fonctionner.
Si elle est destinée à référencer un String déclarer la en String.

De plus dans vos macro vous auriez intérêt à initialiser vos objets Range avec la feuille (éventuellement le classeur) à laquelle ils appartiennent. Je sais que beaucoup ici ne le font pas mais croyez-moi; ça vous évitera dans 6 mois ou 1 an d'avoir à vous rappeler d'où provient cette plage ou cellule. De plus si un utilisateur lance votre macro à partir d'une autre feuille (ou classeur) à laquelle elle est destinée....problèmes.
Il m'est arrivé souvent d'avoir à corriger des macros dont les auteurs n'avait pas pris ces précautions. C'est un peu plus long mais ça vaut le coup.

par exemple la première macro que je vous ai donné deviendrait:
VB:
Sub truc()
    Dim wk as workbook
    Dim idx As Variant
    set wk = ThisWorkbook
    With wk.Sheets("data")
        '
        ' Utilisation de l'équivalent de la fonction EQUIV
        idx = Application.Match(wk.Sheets("mix").Range("B30"), .Range("A1:A23"), 0)
        '
        ' Si Trouvé
        If Not IsError(idx) Then Debug.Print .Cells(idx, 4).Address
    End With
End Sub
Bonne journée
 

douguy

XLDnaute Junior
Bonjour ROBLOCHON, STAPLE

Merci bcp pour vos retours!
du coup j'ai modifié le code comme ceci
ça bloque toujours sur l'adresse. je suis trop léger en VBA pour piger ce qui bloque. Je suis désolé :(

Sub savebudget()

Dim wk As Workbook
Dim idx As Variant
Set wk = ThisWorkbook
Dim plage As Range, C As Range
Dim D As String

With wk.Sheets("data")

Set plage = Range("N20:R51")
If plage.Cells(plage.Cells.Count) <> "" Then
Set C = plage.Cells(plage.Cells.Count)
Else
Set C = plage.Find(what:="*", After:=plage.Cells(plage.Cells.Count - 1), SearchDirection:=xlPrevious)
End If

D = C.adress(0, 0)
End With

With Sheets("mix")
Range("N20:" & D).Copy
End With

With Sheets("data")
.Activate
idx = Application.Match(Sheets("mix").Range("B20"), .Range("A1:A1464"), 0)
If Not IsError(idx) Then .Cells(idx, 106).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
End With

End Sub
 

Roblochon

XLDnaute Accro
Re,

Oui, mais il faut bien lire les réponses qui vous sont données. Je vous ai donné la solution. Dans mon post précédent.
Vous pouvez aussi chercher un tuto pour apprendre la différence entre une variable objet (par exemple Range) et une variable String (un texte).

Cordialement
 

Staple1600

XLDnaute Barbatruc
Re
douguy
En anglais, il y a deux d dans address
C'est ce qu'on pouvait lire dans le message#5 (le mien)
Sans oublier qu'on le lisait déjà dans le message#2 (celui de Roblochon)
 

douguy

XLDnaute Junior
arrrrrgggghhh ! une matinée dans les choux pour un "D"
mais merci Staple et Roblochon.
ca m'apprendra à mieux regarder et au moins ca m'aura forcer a lire des tutos ;)
merci bcp et belle soirée à vous 2
 

Haut Bas