Range avec xlup ou xldown

**potenco** · 08/03/2011, 14h05

Bonjours a tous je sollicite votre aide car je comprend toujours pas comment marche Range(....) xlup ??

Le but est : j'ai un tcd et plusieur cellule plus loin une formule que j'etire jusq'a la derniere celllule sauf que mon tableau peux varier au niveaux des ligne c'est pourquoi j'aimerais que dans ma macro le autofill marche jusqu'a la derniere valeur de mon tableaux

Code :

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

1
2
3
  Range("Y2").Select
    Selection.AutoFill Destination:=Range("Y2;Cells(Rows.Count, c).End(xlUp).Row
    Range("Y2:Y94").Select

Merci beaucoup pour vos reponse .

**mercatog** · 08/03/2011, 20h33

Bonjour

Code :

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

1
2
3
4
5
6
Dim LastLig As Long
 
With Sheets("Feuil2")                                                'à adapter
    LastLig = .Cells(.Rows.Count, "C").End(xlUp).Row                 'Dernière cellule remplie de la colonne C
    If LastLig > 2 Then .Range("Y2").AutoFill .Range("Y2:Y" & LastLig)
End With

**potenco** · 09/03/2011, 08h44

Merci Bcp pour cette reponse cela marche mais si le tableaux se reduit ça ne marche plus donc je n'arrive toujours pas à trouver la solution mais je persevere !!!

**Godzestla** · 09/03/2011, 09h38

Envoyé par potenco

Merci Bcp pour cette reponse cela marche mais si le tableaux se reduit ça ne marche plus donc je n'arrive toujours pas à trouver la solution mais je persevere !!!

Bonjour,

peux-tu expliquer un peu mieux ?
Quelle est la colonne sur laquelle tu veux te baser pour ton test ?
Le code donné par Mercatog semble parfait.

**potenco** · 09/03/2011, 09h57

Alors je m'explique :

-Mon tableaux qui varie commence de la colonne A à la Colonne W (donc à chaque actualisation mon tableaux peux varier de la ligne 1 à je ne sais pas )

-c'est un tableaux dynamique fait avec Query qui trie en fonction du code client

- Ensuite je reprend une formule recherchV qui est situer deux colonne aprés pour pas que ça soit pris dans le tableaux dynamique donc colonne Y

-Je veux que la formule dans la colonne Y suivent la derniere ligne de mon tableaux .

- le souci que j'ai rencontrer avec la solution mercatog est que si lors d'une actualisation mon tableaux à moin de ligne que celui d'avant alors ça me met #ref sur la dernier ligne du tableaux et jusqu'a la derniere ligne de mon ancien tableaux

exemple 1er tableaux qui va jusqu'a la ligne 200
2eme tableaux qui va jusqu'a 100
Alors ça me met #ref de la ligne 100 à 200

Code :

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

1
2
3
4
5
6
7
8
9
10
Range("Y2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-5],'210100S-1'!C[-5]:C[2],6,FALSE)=0,"""",VLOOKUP(RC[-5],'210100S-1'!C[-5]:C[2],6,FALSE))"
 
     Dim LastLig As Long
 
With Sheets("210100")                                                'à adapter
    LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row                 'Dernière cellule remplie de la colonne C
    If LastLig > 2 Then .Range("Y2").AutoFill .Range("Y2:Y" & LastLig)
End With

Merci de m'aider pour ce code ! je suis nouveaux sur VBA donc désoler si je m'exprime mal et encore merci de vos réponses c'est super gentils.

**Godzestla** · 09/03/2011, 10h17

Re,

je comprends mieux maintenant.

Tu peux expliquer comment tu fais ceci ?

-c'est un tableaux dynamique fait avec Query qui trie en fonction du code client

Ton problème vient du fait que tu as des formules plus loin que tes données, et donc c'est cela qu'il faut changer, mais pour cela, j'ai besoin de savoir comment tu insères tes données dans excel et comment tu injecte tes formules.

Merci de montrer l'intégralité de ton code svp.

**potenco** · 09/03/2011, 10h25

Voici l'integralité de mon code en faite pour mon tableaux query il est juste ouvert ce n'est pas la macro qui l'ouvre la macro la met juste a jour.
Ce que j'ai pu comprendre avec l'exmple de mon dernier post c'est que la derniere ligne donc 200 met bien la valeur rechercher mais de 100 à 199 il me met #ref alors que mon nouveaux tableaux s'arrete à 100
(Je precice ce n'est qu'un exemple )

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Sub Macro1()
'
' Macro1 Macro
'
 
 Workbooks.Open Filename:="O:\gti\production1.xls"
 
    Columns("A:T").Select
    Selection.Copy
    ActiveWindow.WindowState = xlMinimized
    Windows("plannification.xls").Activate
    ActiveWindow.WindowState = xlMaximized
   Sheets("bdp").Select
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:="O:\gti\libcompcde1.xls"
    Columns("A:C").Select
    Range("A563").Activate
 
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.WindowState = xlMinimized
    Windows("plannification.xls").Activate
    ActiveWindow.WindowState = xlMaximized
    Sheets("libcomp").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("210100").Select
    Cells.Select
    Range("BS1").Activate
    Selection.Copy
 
    Sheets("210100S-1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("210100").Select
    Range("C4").Select
    Application.CutCopyMode = False
    With Selection.ListObject.QueryTable
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
    End With
 
    ActiveWorkbook.RefreshAll
 
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-5],'210100S-1'!C[-5]:C[2],6,FALSE)=0,"""",VLOOKUP(RC[-5],'210100S-1'!C[-5]:C[2],6,FALSE))"
 
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-6],'210100S-1'!C[-6]:C[1],7,FALSE)=0,"""",VLOOKUP(RC[-6],'210100S-1'!C[-6]:C[1],7,FALSE))"
 
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-7],'210100S-1'!C[-7]:C,8,FALSE)=0,"""",VLOOKUP(RC[-7],'210100S-1'!C[-7]:C,8,FALSE))"
 
  Dim LastLig As Long
 
With Sheets("210100")                                                'à adapter
    LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row                 'Dernière cellule remplie de la colonne C
    If LastLig > 2 Then .Range("Y2").AutoFill .Range("Y2:Y" & LastLig)
End With
 
 
With Sheets("210100")                                                'à adapter
    LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row                 'Dernière cellule remplie de la colonne C
    If LastLig > 2 Then .Range("Z2").AutoFill .Range("Z2:Z" & LastLig)
End With
 
 
With Sheets("210100")                                                'à adapter
    LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row                 'Dernière cellule remplie de la colonne C
    If LastLig > 2 Then .Range("AA2").AutoFill .Range("AA2:AA" & LastLig)
End With
 
 
End Sub

Désolé pour le tableaux dynamique je fait:
-donnée/query/fichier exel / je selectionne mon classeur planiffication je choisie BDP(la ou son mes valeur de production ) puis je trie en fonction du code client puis j'ouvre dans la feuille existante "210100"

J'espere être assez clair . Encore mercie pour votre aides je lutte depuis 3 jour

**Godzestla** · 09/03/2011, 10h45

Désolé pour le tableaux dynamique je fait:
-donnée/query/fichier exel / je selectionne mon classeur planiffication je choisie BDP(la ou son mes valeur de production ) puis je trie en fonction du code client puis j'ouvre dans la feuille existante "210100"

J'espere être assez clair . Encore mercie pour votre aides je lutte depuis 3 jour

Peux-tu démarrer l'enregistreur de macro, faire cette opération d'import complète, et puis donner ici le code ainsi généré.

Car, oui, cela aussi on peut l'automatiser.

**potenco** · 09/03/2011, 10h54

Envoyé par Godzestla

Peux-tu démarrer l'enregistreur de macro, faire cette opération d'import complète, et puis donner ici le code ainsi généré.

Car, oui, cela aussi on peut l'automatiser.

Oui je peux le faire mais je m'explique :

Avant de lancer ma macro je dois noter des information qui sont copier en S-1
puis aprés faire une actualisation c'est pourquoi je n'est pas fait de macro de query sinon mes donnés allez etre actualiser avant même de lancer ma macro

Veux tu que je le fasse quand même ?

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
34
35
Sub Macro1()
'
' Macro1 Macro
'
 
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Excel Files;DBQ=O:\gti\Planification.xls;DefaultDir=O:\gti;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `'210100$'`.SSAA_Fab, `'210100$'`.OF_LIG_IND, `'210100$'`.Lig_Nomenc, `'210100$'`.Qte_Cde, `'210100$'`.Del_expé, `'210100$'`.Code_PF, `'210100$'`.N_Plan, `'210100$'`.`Designat°`, `'210100$'`.Tp" _
        , _
        "s_Prevu, `'210100$'`.Tps_Real, `'210100$'`.Reste_Fab, `'210100$'`.Valeur_Cde, `'210100$'`.Stock_PF, `'210100$'`.Nom, `'210100$'`.Client, `'210100$'`.Ref_Cde1, `'210100$'`.Code_Barre, `'210100$'`.Code_" _
        , _
        "Compo, `'210100$'`.F19, `'210100$'`.F20, `'210100$'`.F21, `'210100$'`.F22, `'210100$'`.`Ref CDE 2`, `'210100$'`.`outillage `, `'210100$'`.CTRL, `'210100$'`.`Nb OF`, `'210100$'`.Remarque" & Chr(13) & "" & Chr(10) & "FROM `O:\gti\" _
        , _
        "Planification.xls`.`'210100$'` `'210100$'`" & Chr(13) & "" & Chr(10) & "WHERE (`'210100$'`.Code_Compo='210100')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With
 
End Sub

Voici le code demander merci de votre aide encore une fois

**Godzestla** · 09/03/2011, 11h17

Avant de lancer ma macro je dois noter des information qui sont copier en S-1
puis aprés faire une actualisation c'est pourquoi je n'est pas fait de macro de query sinon mes donnés allez etre actualiser avant même de lancer ma macro

Pas d'accord.
C'est quand tu appelleras la macro, par Run.... ou avec un bouton que tes données se mettrons à jour. Rien à voir avec des liens automatiques.

**potenco** · 09/03/2011, 11h23

D'accord je vois la macro que j'ai faite de mon tableaux vous convient-il ? je dois en faire quoi ?

**Godzestla** · 09/03/2011, 11h40

Questions:

peut-on travailler comme ceci :

0) on fait toute les opérations depuis le fichier ou tu veux traiter ton tableau que tu appelles dynamique

1) Quand tu as noté ce que tu souhaite (S-1) tu lances le code qui :
2) Importe les données du fichier O:\gti\Planification.xls MAIS TU DOIS ME PRECISER SUR QUELLE FEUILLE TU VEUX LE RESULTAT

Ensuite dans ton code de départ, je vois que tu ouvres le fichier O:\gti\Planification.xls, puis que tu fais des copy paste. je ne comprends pas carle query fait cela pour toi. Il te reste juste à ajouter tes formules jusqu'à la dernière ligne importée.

3) Peut-on avant l'import effacer toute la feuille destination pour éviter les #REF ?

**potenco** · 09/03/2011, 12h11

Réponse

0) Comprend pas trop

désolé mais je peux dire:
-j'ai un classeur nommé planification dont je renome toute les feuilles c'est a dire :
-1ere feuille BDP(copie de donnés du fichier production là ou son mes données qui varie )
-2eme feuille 210100(là où j'insere mon tableaux dynamiquequi va venir chercher sur le fichier panification.xls les donnée de BDP puis les trie )
-3eme feuille 210100S-1 (la ou je fais une copie de la feuille 210100)

Maintenant ordre du travail a effectuer

- j'ouvre mon fichier planification.xls
-je tape dans une colonne de la feuille 210100 (en dehors de mon tableaux ) ce que je dois taper exemple: "ok, en achat, etc.." pour chaque lignes du tableaux
-je lance ma macro qui doit faire en premier lieux une copie de ma feuille 210100 avec les information notée (ok en achat etc ) sur la feuille 210100S-1 puis ensuite la macro va ouvrir le fichier production.xls qu'elle va copier dans la feuille BDP (ou les valeur sur celui ci aurons variés ) et aprés j'actualise mon tableaux par consequent les valeurs vont changer puis ma colone ou il y avait ecrit "achat ok etc" je vais ecrire la formule rechercheV qui elle va chercher sur la feuille 210100S-1 les valeurs (ok; achat etc ) si elle trouve pas les numero pas grave ça doit mettre #NA .

J'espere avoir etait assez claire merci de prendre le temps de comprendre mon code !

**Godzestla** · 09/03/2011, 12h48

... bof bof,

te rends tu compte que plus tu expliques, plus c'est nébuleux, et que tout cela n'a plus rien à voir du tout avec ta demande du début (End.(Xlup)°?

**potenco** · 09/03/2011, 13h13

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
34
35
36
37
38
39
Sub Macro1()
'
' Macro1 Macro
'
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Excel Files;DBQ=O:\gti\Planification.xls;DefaultDir=O:\gti;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `'210100$'`.SSAA_Fab, `'210100$'`.OF_LIG_IND, `'210100$'`.Lig_Nomenc, `'210100$'`.Qte_Cde, `'210100$'`.Del_expé, `'210100$'`.Code_PF, `'210100$'`.N_Plan, `'210100$'`.`Designat°`, `'210100$'`.Tp" _
        , _
        "s_Prevu, `'210100$'`.Tps_Real, `'210100$'`.Reste_Fab, `'210100$'`.Valeur_Cde, `'210100$'`.Stock_PF, `'210100$'`.Nom, `'210100$'`.Client, `'210100$'`.Ref_Cde1, `'210100$'`.Code_Barre, `'210100$'`.Code_" _
        , _
        "Compo, `'210100$'`.F19, `'210100$'`.F20, `'210100$'`.F21, `'210100$'`.F22, `'210100$'`.`Ref CDE 2`, `'210100$'`.`outillage `, `'210100$'`.CTRL, `'210100$'`.`Nb OF`, `'210100$'`.Remarque" & Chr(13) & "" & Chr(10) & "FROM `O:\gti\" _
        , _
        "Planification.xls`.`'210100$'` `'210100$'`" & Chr(13) & "" & Chr(10) & "WHERE (`'210100$'`.Code_Compo='210100')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_Excel_Files"
        .Refresh BackgroundQuery:=False
 
 
 
 
 
    End With
 
End Sub

On va faire plus simple ici j'ai mis la macro de mon tableaux
1) On est d'accord si je change le nombre de ligne de mon fichier qui construit ce tableaux cela va changer le nombre de ligne ?

2) Deux colonne plus loin (colonne AD) de se tableaux j'ecrit la formule toute simple en AD2 = 1 et AD3= 1+AD2

Question comment faire suivre la formule de AD3 si le tableaux varie ?

**Godzestla** · 09/03/2011, 13h49

Ok pour plus simple.

je te conseille avant de copie les nouvelles données vers ta feuille résultat de faire les opération suivante, depuis un module du fichier résultat

Je nomme pour ma facilité ta feuille résulalt "RES"
1) Supprimer les éventuels SQl précédents de cette feuille

Code :

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

1
2
3
4
5
 
Dim sq As QueryTable
For Each sq In Thisworkbook.Worksheets("RES").QueryTables
    sq.Delete
Next

2) Effacer toutes les cellules

Code :

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

thisworkbook.Worksheets("RES").Clearcontents

Ensuite , tu intègres tes données à ta manière, par exemple avec le code de Query que tu as donné ci-avant.

3) Tu intègres tes formules en AD2 et AD3.

4) tu utilises la technique de mercatog qui donne toujours d'EXCELLENTS conseil pour étendre tes formules jusqu'a End(xlUp)

Et puis voilà.

Range avec xlup ou xldown

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager