Array à partir d'une union de range

[avaya]

Bonjour,

J'ai une question sans doute très simple à répondre mais je ne la trouve pas par moi-même.

Sub Macro1()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range

Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")

monTab = Application.Union(plage1, plage2).Value

MsgBox monTab(1, 1) 'Ca marche!
MsgBox monTab(1, 2) 'Ca ne marche pas... : l'indice n'appartient pas à la selection

End Sub

Pourquoi mon tableau n'a que la plage 1?
(Et comment faire pour que le tableau ait aussi la plage 2?)

Merci d'avance,

avaya

 

[job75]

Re : Array à partir d'une union de range

Bonjour avaya,

Eh oui, les tableaux VBA ne se laissent pas manipuler comme les plages !

Sub Macro1()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range

Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")

Application.ScreenUpdating = True
With Workbooks.Add.Sheets(1) 'document auxiliaire
  .[A1].Resize(plage1.Rows.Count, plage1.Columns.Count) = plage1.Value
  .[A1].Offset(, plage1.Columns.Count).Resize(plage2.Rows.Count, plage2.Columns.Count) = plage2.Value
  monTab = .UsedRange.Value
  .Parent.Close False
End With

MsgBox monTab(1, 1) 'Ca marche!
MsgBox monTab(1, 2) 'Ca marche!!

End Sub

A+

 

[Pierrot93]

Re : Array à partir d'une union de range

Bonjour à tous,

une autre approche :

Option Explicit
Sub test()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
With Application
    monTab = .Transpose((.Transpose(Array(.Transpose(plage1.Value), .Transpose(plage2.Value)))))
End With
MsgBox monTab(2, 2)
End Sub

bon après midi
@+

 

[job75]

Re : Array à partir d'une union de range

Hello Pierrot,

Bonne idée, mais ta macro nécessite que les 2 plages n'aient qu'une colonne.

A+

 

[Pierrot93]

Re : Array à partir d'une union de range

Re,

Bonne idée, mais ta macro nécessite que les 2 plages n'aient qu'une colonne.

oui comme le problème rencontré par notre ami... pas cherché sur plusieurs colonnes...

 

[avaya]

Re : Array à partir d'une union de range

Bonjour à vous deux,

Déjà merci pour votre réactivité!

A job75: ton idée est très intéressante car elle est effectivement adaptable à tout type de situation.

A Pierrot93: ton idée correspond à mon exemple sauf que j'ai oublié de préciser qu'il y aura d'autres plages 3, 4... également d'une colonne à inclure dans l'array. Est-ce que cela pourrait se faire dans ta macro?
Je ne connaissais pas le Transpose dans VBA. Par curiosité, j'aimerais savoir pourquoi on doit faire

.Transpose(.Transpose(Array.

Parce que, si j'ai bien compris:

.Transpose(plage1.Value)

La Plage1 de 2 lignes devient Plage1 de 2 colonnes

Array(.Transpose(plage1.Value), .Transpose(plage2.Value))

On a un array(2,2)...

Avaya

 

[Pierrot93]

Re : Array à partir d'une union de range

Re,

Je ne connaissais pas le Transpose dans VBA. Par curiosité, j'aimerais savoir pourquoi on doit faire

décompose le code et observe dans la fenêtre des variables locales en executant le code pas à pas, le redimensionnement du tableau...

With Application
    monTab = Array(.Transpose(plage1.Value), .Transpose(plage2.Value))
    monTab = .Transpose(monTab)
    monTab = .Transpose(monTab)
End With

 

[avaya]

Re : Array à partir d'une union de range

Merci pour ton conseil.
Je ne savais pas qu'on pouvait suivre ce qui se passe avec une fenêtre (il y a beaucoup de choses que je ne sais pas ).
Avant j'utilisais F8 mais je laissais le curseur sur les variables pour voir ce que ça donne sauf que pour des tableaux, ce n'est pas pratique.

Du coup, je suis arrivée à faire marcher la macro pour 3 plages:

Sub testPierrot()
Dim monTab As Variant
Dim montab2 As Variant
Dim x1 As Integer, x2 As Integer
Dim plage1 As Range
Dim plage2 As Range
Dim plage3 As Range
Dim i As Integer
t = Timer
For i = 1 To 100
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
Set plage3 = Range("E1:E2")
With Application
    monTab = Array(.Transpose(plage1.Value), .Transpose(plage2.Value), .Transpose(plage3.Value))
    monTab = .Transpose(monTab)
    monTab = .Transpose(monTab)
End With
ReDim montab2(UBound(monTab, 2), UBound(monTab, 1))
For x1 = 1 To UBound(monTab, 2)
For x2 = 1 To UBound(monTab, 1)
    montab2(x1, x2) = monTab(x2, x1)
Next x2
Next x1
Next i
MsgBox Timer - t
MsgBox montab2(2, 2)
MsgBox montab2(2, 3)
End Sub

Et celle de job75:

Sub testjob()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range
Dim plage3 As Range
Dim i As Integer
t = Timer
For i = 1 To 100
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
Set plage3 = Range("E1:E2")

Application.ScreenUpdating = True
With Workbooks.Add.Sheets(1) 'document auxiliaire
  .[A1].Resize(plage1.Rows.Count, plage1.Columns.Count) = plage1.Value
  .[A1].Offset(, plage1.Columns.Count).Resize(plage2.Rows.Count, plage2.Columns.Count) = plage2.Value
  .[A1].Offset(, plage1.Columns.Count + plage2.Columns.Count).Resize(plage3.Rows.Count, plage3.Columns.Count) = plage3.Value
  monTab = .UsedRange.Value
  .Parent.Close False
End With
Next i
MsgBox Timer - t
MsgBox monTab(1, 1) 'Ca marche!
MsgBox monTab(2, 3) 'Ca marche!!

End Sub

La tienne va beaucoup, beaucoup plus vite que celle de job alors je la choisis pour ma macro.

Merci encore

avaya

 

[BOISGONTIER]

Re : Array à partir d'une union de range

Bonjour,


Quel est la finalité?. s'il s'agit d'explorer tous les éléments de l'union:

Sub essai1()
 Set plage1 = Range("A1:A2")
 Set plage2 = Range("C1:C2")
 For Each c In Union(plage1, plage2)
   MsgBox c
 Next c
End Sub

Pour récupérer dans un tableau: avec une fonction perso fusion() (tableaux ou champs)

Sub essai3()
 a = Range("A1:A2")
 b = Range("C1:C2")
 c = Fusion(a, b)
 [e1].Resize(UBound(c)) = c
End Sub

NB:Les tableaux a() et b() peuvent avoir des longueurs différentes

ou

Sub essai4()
 c = Fusion(Range("A1:A2"), Range("c1:c2"))
 [g1].Resize(UBound(c)) = c
End Sub

Pour 4 tableaux:

  a = [tab1].Value
  b = [tab2].Value
  c = [tab3].Value
  d = [tab4].Value
  e = Fusion(Fusion(a, b,c), d)

http://boisgontierjacques.free.fr/fichiers/Cellules/FusionTableaux.xls
JB

 

[job75]

Re : Array à partir d'une union de range

Re,

Avec plusieurs plages d'une colonne (et de même hauteur) un remplissage par boucles est facile :

Sub Macro1()
Dim plage As Range, h&, monTab(), j%, a As Range, i&

Set plage = [A1:A2,C1:C2,E1:E2,G1:G2]
h = plage.Areas(1).Count

ReDim monTab(1 To h, 1 To plage.Areas.Count)

For j = 1 To plage.Areas.Count
  Set a = plage.Areas(j)
  For i = 1 To h
    monTab(i, j) = a(i)
  Next
Next
  
MsgBox monTab(1, 1)
MsgBox monTab(2, 4)

End Sub

Edit : salut JB

A+

 

[job75]

Re : Array à partir d'une union de range

Re,

Sur Excel 2013 j'ai testé avec 4 colonnes de 1 000 000 de lignes remplies de 1.

La macro s'exécute en 16 secondes.

En remplaçant le Range "a" par un tableau VBA la durée se réduit à 1,6 seconde :

Sub Macro1()
Dim t#, plage As Range, h&, monTab(), j%, a, i&

t = Timer
Set plage = [A1:A1000000,C1:C1000000,E1:E1000000,G1:G1000000]
h = plage.Areas(1).Count

ReDim monTab(1 To h, 1 To plage.Areas.Count)

For j = 1 To plage.Areas.Count
  a = plage.Areas(j)
  For i = 1 To h
    monTab(i, j) = a(i, 1)
  Next
Next

MsgBox Timer - t

End Sub

A+

 

[avaya]

Re : Array à partir d'une union de range

Bonjour,

Pour Boisgontier:
En fait, j'ai des plages discontinues mais je voudrais traiter ces plages en ligne.
Les plages sont des colonnes avec le même nombre d'éléments.

Pour aller plus vite, j'ai pensé que ça serait bien de fusionner les plages dans un tableau mais un tableau de deux dimensions.
Mais à ce que je vois, job75 a fait une suggestion qui correspond bien à ce que je souhaite.
Je te remercie quand même pour ton aide!

Pour job:
Ca va encore plus vite et c'est plus facile à utiliser puisque je n'ai pas besoin de mettre plein de variables.
Merci beaucoup beaucoup!

avaya

 

[BOISGONTIER]

Re : Array à partir d'une union de range

Bonjour,

Cette méthode doit être + rapide

Sub Essai()
  ncol = 10
  h = 2
  Dim montab(): ReDim montab(h, ncol)
  a = [A1].Resize(h, ncol * 2)         ' lecture dans un tableau a()
  For c = 1 To ncol                        
    For i = 1 To h
       montab(i, c) = a(i, c * 2 - 1)    ' on prend 1 colonne sur 2
    Next i
  Next c
  MsgBox montab(1, 1)
  MsgBox montab(2, 3)
End Sub

Si tu veux passer par une plage discontinue variable

Sub UnionDynamique()
  Set plage = Range("A1:A4")
  ncol = 10
  h = plage.Count
  intervalle = 2
  For col = 1 + intervalle To ncol * intervalle Step intervalle
    Set plage = Union(plage, Cells(1, col).Resize(h))
  Next col
  MsgBox plage.Address
End Sub

JB

 

[avaya]

Re : Array à partir d'une union de range

Re,

A JB:
Bonne méthode sauf que j'ai oublié de préciser que les colonnes ne sont pas forcément séparées par une seule colonne (et parfois elles peuvent être collées les unes aux autres). Je m'excuse pour cet oubli!

A job:
Finalement ça ne fonctionne pas car j'ai oublié de préciser que parfois, les plages peuvent être collées les unes aux autres. Et dans ce cas là, après l'union, le Area va me compter une seule plage au lieu de deux (si on prend le cas d'une union de deux plages collées)...
Du coup j'ai modifié la macro pour que ça fonctionne:
En supposant que Plage5 et Plage6 sont collées.

Private Sub RemplirTableauFLVL()
Dim MonTab()
Dim j As Integer, i As Integer, k As Integer, n As Integer, y As Integer, Lastline as Integer
Set Plage = Union(Plage1, Plage2, Plage3, Plage4, Plage5, Plage6, Plage7)
LastLine = Plage.Areas(1).Count
ReDim TableauFLVL(1 To LastLine, 1 To 7)
y = 1
For j = 1 To Plage.Areas.Count
    n = 1
    MonTab = Plage.Areas(j)
    MsgBox ("Pour la plage numéro " & j & "    la limite est " & UBound(MonTab, 2))
    For k = y To UBound(MonTab, 2) + y - 1
    For i = 1 To LastLine
    TableauFLVL(i, k) = MonTab(i, n)
    Next i
    n = n + 1
    Next k
    If UBound(MonTab, 2) > 1 Then y = y + 1
    y = y + 1
Next j
End Sub

Mais je trouve ça assez moyen parce que si la plage5 était par exemple collée à la plage7, ça ne fonctionnerait pas.
Il faudrait changer l'emplacement dans la macro...
Je pars du principe qu'un utilisateur pourrait un jour peut être changer la position des colonnes de la feuille Excel et qu'il n'aura pas forcément conscience des enjeux de tels changements sur la macro...
Du coup je vais repartir sur l'idée de Pierrot qui ne demandait pas de prendre en compte tous ces cas de figure.


Edit:
Erreur de ma part. Le code de job fonctionne bien!


Merci encore,

avaya

 

[job75]

Re : Array à partir d'une union de range

Bonjour,

A job:
Finalement ça ne fonctionne pas car j'ai oublié de préciser que parfois, les plages peuvent être collées les unes aux autres. Et dans ce cas là, après l'union, le Area va me compter une seule plage au lieu de deux (si on prend le cas d'une union de deux plages collées)...

Avez-vous testé ??? Car chez moi (Excel 2013) aucun problème avec :

Set plage = [A1:A2,B1:B2,D1:D2]

Chaque Area (colonne) doit évidemment être séparée par une virgule...

Fichier joint.

A+