definition de variable difficile

[flo2002]

Re bonjour,

je suis en train de passer toutes mes macros avec des variables et j'appel au secours.
voici mes variables:
Dim c11, c21, c31, c41, c51, c12, c22, c32, c42, c52
Dim feuil1 As String, feuil2 As String, Feuil3 As String, Feuil4 As String, Feuil5 As String

feuil1 = Sheets("Garde").Range("g3").Value
feuil2 = Sheets("Garde").Range("g4").Value
Feuil3 = Sheets("Garde").Range("g5").Value
Feuil4 = Sheets("Garde").Range("g6").Value
Feuil5 = Sheets("Garde").Range("g7").Value

Dim Exist As Byte
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Set ws1 = Sheets(feuil1)
Set ws2 = Sheets("lien")
Set ws3 = Sheets(Feuil3)
Set ws4 = Sheets(Feuil4)
Set ws5 = Sheets(Feuil5)
Set ws6 = Sheets(feuil2)

le probleme est que il est possible que j'ai une feuille qui n'existe pas. Donc j'ai une feuille nommée "". et donc bug.
Je voudrai eviter que ca plante a ce niveau car apres dans mes macros j'ai des on error goto...

merci d'avance

 

[michel_m]

Re : definition de variable difficile

Bonjour

avant d'instancier les worksheets (set..), compte le nombre de feuilles et ensuite instancie avec une condition

A+
Michel_M

 

[flo2002]

Re : definition de variable difficile

Merci,
mon probleme est que j'ai un nombre variable de feuille selon le fichier.
Entre 1 et 5.
pour ce probleme j'ai mis on error goto. et vu que le bug doit arrivé en dernier c'est bon.
par contre ca ne marche pas pour le code suivant:
On Error GoTo out1
c11 = ws1.Range("B65536").End(xlUp).Row
c12 = ws1.Range("D65536").End(xlUp).Row

c21 = ws2.Range("A65536").End(xlUp).Row
c22 = ws2.Range("C65536").End(xlUp).Row

c31 = ws3.Range("B65536").End(xlUp).Row
c32 = ws3.Range("D65536").End(xlUp).Row

c41 = ws4.Range("B65536").End(xlUp).Row
c42 = ws4.Range("D65536").End(xlUp).Row

c61 = ws6.Range("B65536").End(xlUp).Row
c62 = ws6.Range("D65536").End(xlUp).Row

c51 = ws5.Range("B65536").End(xlUp).Row
c52 = ws5.Range("D65536").End(xlUp).Row

out1:

merci!

 

[michel_m]

Re : definition de variable difficile

Attend! ch'uis perdu!

Tu as un nombre variable de feuilles, tu as par exemple 5 feuilles...

Dans ton code précédé de "on error goto", on va aller à out1 quand le prog va renconter ws6 et on ne prendra pas en compte ws5 ?

sinon, quel est le message d'erreur et sur quelle ligne apparait-il ?

dans l'attente,

Michel

 

[flo2002]

Re : definition de variable difficile

il me parle de ca:

Variable d'objet ou de bloc non définit, ce qui est vrai puisque ws5 n'existe pas.
cependant il devrait prendre on error goto. non?
merci d'avance

 

[Kobaya]

Re : definition de variable difficile

salut flo,

la solution est peut-être de créer un tableau de WorkSheets :

Sub test()
    Dim wshFeuille() As Worksheet
    Dim intNbFeuilles As Integer
    Dim intIndex As Integer
    Dim intIndex2 As Integer
    
    intNbFeuilles = Sheets.Count
    intIndex2 = 0
    ReDim wshFeuille(intNbFeuilles - 2)
    For intIndex = 1 To intNbFeuilles
        If Sheets(intIndex).Name <> "Garde" Then
            Set wshFeuille(intIndex2) = Sheets(intIndex)
            intIndex2 = intIndex2 + 1
        End If
    Next
    
    ' libérer la mémoire utilisée par les variables
    For intIndex = 0 To intIndex2 - 1
        Set wshFeuille(intIndex) = Nothing
    Next
End Sub

qu'en penses-tu ?

 

[michel_m]

Re : definition de variable difficile

re re Flo, bonjour Kobaya (moi c'est Michel)

l'idée du tableau de Kobaya est intéressante et en y incorporant les variables C51 C52... etc avec la recherche de dernière ligne en type string, on ferait alors appel qu'aux feuilles désirées...
A+ (il faut que je parte)
Michel_m

 

[flo2002]

Re : definition de variable difficile

Je vous avoue que cette affaire de tableau ne me parle pas du tout!
je ne vois pas comment la mettre en place?

je dois l'integrer dans ma sub?


ps voici la bete:

Option Explicit

Sub consolidation()


Cells.Select

Selection.RemoveSubtotal

Application.ScreenUpdating = False



Dim c11, c21, c31, c41, c51, c12, c22, c32, c42, c52, c61, c62
Dim feuil1 As String, feuil2 As String, Feuil3 As String, Feuil4 As String, Feuil5 As String

feuil1 = Sheets("Garde").Range("g3").Value
feuil2 = Sheets("Garde").Range("g4").Value
Feuil3 = Sheets("Garde").Range("g5").Value
Feuil4 = Sheets("Garde").Range("g6").Value
Feuil5 = Sheets("Garde").Range("g7").Value

Dim Exist As Byte
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet

On Error GoTo out333

Set ws2 = Sheets("lien")
Set ws1 = Sheets(feuil1)
Set ws3 = Sheets(Feuil3)
Set ws4 = Sheets(Feuil4)
Set ws6 = Sheets(feuil2)
Set ws5 = Sheets(Feuil5)

out333:
On Error GoTo out1
c11 = ws1.Range("B65536").End(xlUp).Row
c12 = ws1.Range("D65536").End(xlUp).Row

c21 = ws2.Range("A65536").End(xlUp).Row
c22 = ws2.Range("C65536").End(xlUp).Row

c31 = ws3.Range("B65536").End(xlUp).Row
c32 = ws3.Range("D65536").End(xlUp).Row

c41 = ws4.Range("B65536").End(xlUp).Row
c42 = ws4.Range("D65536").End(xlUp).Row

c61 = ws6.Range("B65536").End(xlUp).Row
c62 = ws6.Range("D65536").End(xlUp).Row

c51 = ws5.Range("B65536").End(xlUp).Row
c52 = ws5.Range("D65536").End(xlUp).Row

out1:
On Error GoTo out2
ws2.Visible = True
ws2.Select
Range("A:A").Clear
Range("C:C").Clear
ws1.Select
Range("B10:B" & c11).Select
Selection.Copy
ws2.Select
Cells(c21, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

c21 = ws2.Range("A65536").End(xlUp).Row

ws3.Select
Range("B10:B" & c31).Select
Selection.Copy
ws2.Select
Cells(c21 + 1, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


out2:
On Error GoTo out3
c21 = ws2.Range("A65536").End(xlUp).Row
ws4.Select
Range("B10:B" & c41).Select
Selection.Copy
ws2.Select
Cells(c21 + 1, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


c21 = ws2.Range("A65536").End(xlUp).Row
ws5.Select
Range("B10:B" & c51).Select
Selection.Copy
ws2.Select
Cells(c21 + 1, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

out3:
On Error GoTo out4
c22 = ws2.Range("C65536").End(xlUp).Row
ws1.Select
Range("D10😀" & c12).Select
Selection.Copy
ws2.Select
Cells(c22, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

c22 = ws2.Range("C65536").End(xlUp).Row


ws3.Select
Range("D10😀" & c32).Select
Selection.Copy
ws2.Select
Cells(c22 + 1, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


out4:
On Error GoTo out5
c22 = ws2.Range("C65536").End(xlUp).Row
ws4.Select
Range("D10" & c42).Select
Selection.Copy
ws2.Select
Cells(c22 + 1, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


c22 = ws2.Range("C65536").End(xlUp).Row
ws5.Select
Range("D10" & c52).Select
Selection.Copy
ws2.Select
Cells(c22 + 1, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

out5:
On Error GoTo out6
c22 = ws2.Range("C65536").End(xlUp).Row
ws6.Select
Range("D10" & c62).Select
Selection.Copy
ws2.Select
Cells(c22 + 1, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


c22 = ws2.Range("C65536").End(xlUp).Row
ws6.Select
Range("D10" & c62).Select
Selection.Copy
ws2.Select
Cells(c22 + 1, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

out6:
On Error GoTo out7
ws2.Select
Range("a:a").Select
Selection.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("c:c").Select
Selection.Sort Key1:=Range("c1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
out7:
Range("A1").Select
Do While ActiveCell.Value = ""
Selection.Delete Shift:=xlUp
Loop

Range("C1").Select
Do While ActiveCell.Value = ""
Selection.Delete Shift:=xlUp
Loop


Calculate

Dim n As Integer
Dim lign As Integer
Dim col As Collection

Set col = New Collection
For n = 1 To Range("A65536").End(xlUp).Row
On Error Resume Next
col.Add Range("A" & n), CStr(Range("A" & n))
On Error GoTo 0
Next n
lign = 1
For n = 1 To col.Count
Range("B" & lign) = col(n)
lign = lign + 1
Next n



Set col = New Collection
For n = 1 To Range("C65536").End(xlUp).Row
On Error Resume Next
col.Add Range("C" & n), CStr(Range("C" & n))
On Error GoTo 0
Next n
lign = 1
For n = 1 To col.Count
Range("D" & lign) = col(n)
lign = lign + 1
Next n


Calculate



ws2.Select
Dim derligne1%, derligne2%
Dim i1%, i2%

derligne1 = Sheets("Conso_Dpt").Range("f65536").End(xlUp).Row
derligne2 = ws2.Range("d65536").End(xlUp).Row
For i2 = 1 To derligne2
For i1 = 14 To derligne1
If Sheets("Conso_Dpt").Range("f" & i1) = ws2.Range("d" & i2) Then
Exist = 1
GoTo Suivant
End If
Next
If Exist = 1 Then GoTo Suivant
Sheets("Conso_Dpt").Range("f" & derligne1 + 1) = ws2.Range("d" & i2)
derligne1 = Sheets("Conso_Dpt").Range("f65536").End(xlUp).Row
Suivant:
Exist = 0
Next

Sheets("Conso_Dpt").Select
Rows(13).Hidden = False

derligne1 = Range("E65536").End(xlUp).Row
derligne2 = Range("F65536").End(xlUp).Row


Range("A13:E13").Copy
Range(Cells(derligne2 + 1, 1), Cells(derligne1 + 1, 5)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



Range("G13:GF13").Copy
Range(Cells(derligne2 + 1, 7), Cells(derligne1 + 1, 188)).Select

ActiveSheet.Paste
Application.CutCopyMode = False

Rows(13).Hidden = True
Sheets("Conso_Dpt").Select

Calculate
Range("A14:GF1000").Select
Selection.Copy
Range("A14:GF1000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Range("F13").Select
Selection.Copy
Range(Cells(14, 6), Cells(derligne2, 6)).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False



Sheets("Conso_Dpt").Select
Range("A14😀O1000").Select
Selection.Sort Key1:=Range("E14"), key2:=Range("G14"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom




ws2.Visible = False
Application.ScreenUpdating = True
Sheets("Conso_Dpt").Select

Application.DisplayAlerts = False



Range(Cells(14, 1), Cells(derligne2 + 2, 200)).Select

Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(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, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, _
112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131 _
, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, _
151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170 _
, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(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, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, _
112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131 _
, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, _
151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170 _
, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True



Application.DisplayAlerts = True

End Sub