Probleme Variable not defined [RESOLU]

Probleme Variable not defined [RESOLU] - VB/VBA/VBS - Programmation

Marsh Posté le 04-09-2008 à 11:02:21    

bonjour a tous,  
j'ai cette macro ci dessous et il y a un probleme de variable non definie au niveau de la partie de couleur rouge.
Que faut-il que je fasse? Avec cette erreur, je ne peux pas lancer la macro.
 
 

Code :
  1. Option Explicit
  2. Sub PickUpdataMonth(WeekNum, WeekYear, MonthSheet, MonthFormat)
  3. ''' Index '''
  4. Dim IndexFor        As Integer
  5. Dim IndexRow        As Integer
  6. Dim IndexCol        As Integer
  7. ''' Constants Stations Files '''
  8. Dim SRowFaultStart  As Integer
  9. Dim SRangeTot       As String
  10. Dim SRange1st       As String
  11. Dim SRangeAll       As String
  12. Dim SColFault       As Integer
  13. Dim SColNumber      As Integer
  14. Dim SColPercent     As Integer
  15. Dim SColFault2      As Integer
  16. Dim SColNumber2     As Integer
  17. Dim SColPercent2    As Integer
  18. ''' Constants Overall File Data sheet '''
  19. Dim DRowWeek        As Integer
  20. Dim DRowHeader      As Integer
  21. Dim DRowCG          As Integer
  22. Dim DRowSB          As Integer
  23. Dim DRowSH          As Integer
  24. Dim DRowLB          As Integer
  25. Dim DRowOC          As Integer
  26. Dim DRowCC          As Integer
  27. Dim DRowCT          As Integer
  28. Dim DRowCGStart     As Integer
  29. Dim DRowSBStart     As Integer
  30. Dim DRowSHStart     As Integer
  31. Dim DRowLBStart     As Integer
  32. Dim DRowOC1Start    As Integer
  33. Dim DRowOC2Start    As Integer
  34. Dim DRowCCStart     As Integer
  35. Dim DRowCT1Start    As Integer
  36. Dim DRowCT2Start    As Integer
  37. ''' Constants Overall File Lion sheet '''
  38. Dim LRowTopStart        As Integer
  39. Dim LColWeekFault       As Integer
  40. Dim LColWeekNumber      As Integer
  41. Dim LColWeekPercent     As Integer
  42. Dim LColMonthFault      As Integer
  43. Dim LColMonthNumber     As Integer
  44. Dim LColMonthPercent    As Integer
  45. Dim LColStart           As Integer
  46. Dim LColEnd             As Integer
  47. Dim LRowStart           As Integer
  48. Dim LRowEnd             As Integer
  49. ''' Index '''
  50. Dim DIndexColTot    As Integer
  51. Dim DIndexCol1st    As Integer
  52. Dim DIndexColAll    As Integer
  53.     '-----------------------------------------------------------------------------------------------------'
  54.     ''' Init Constants Stations Files '''
  55.     SRowFaultStart = 5
  56.     SRangeTot = "L5"
  57.     SRange1st = "L6"
  58.     SRangeAll = "L7"
  59.     SColFault = 1
  60.     SColNumber = 2
  61.     SColPercent = 3
  62.     SColFault2 = 5
  63.     SColNumber2 = 6
  64.     SColPercent2 = 7
  65.    
  66.     ''' Init Constants Overall File Data Sheet '''
  67.     DRowWeek = 1
  68.     DRowHeader = 2
  69.     DRowCG = 3
  70.     DRowSB = 4
  71.     DRowSH = 5
  72.     DRowLB = 6
  73.     DRowOC = 7
  74.     DRowCC = 8
  75.     DRowCT = 9
  76.     DRowCGStart = 10
  77.     DRowSBStart = 15
  78.     DRowSHStart = 20
  79.     DRowLBStart = 25
  80.     DRowOC1Start = 30
  81.     DRowOC2Start = 35
  82.     DRowCCStart = 40
  83.     DRowCT1Start = 45
  84.     DRowCT2Start = 50
  85.    
  86.     ''' Constants Overall File Lion sheet '''
  87.     LRowTopStart = 51
  88.     LColWeekFault = 1
  89.     LColWeekNumber = 5
  90.     LColWeekPercent = 7
  91.     LColMonthFault = 13
  92.     LColMonthNumber = 17
  93.     LColMonthPercent = 19
  94.     LColStart = 5
  95.     LColEnd = 19
  96.     LRowStart = 31
  97.     LRowEnd = 48
  98.    
  99.    
  100.     ''' Init '''
  101.     DIndexColTot = 2
  102.     DIndexCol1st = 3
  103.     DIndexColAll = 4
  104.    
  105.     '-----------------------------------------------------------------------------------------------------'
  106.     ''' Turn off the screen updating '''
  107.     Application.ScreenUpdating = False
  108.     '-----------------------------------------------------------------------------------------------------'
  109.     ''' ClearContents old data in sheet "Data" '''
  110.     Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexColTot) & DRowCG & ":" & ColLetter_from_ColNumber(DIndexColAll) & DRowCT2Start + 4).ClearContents
  111. Dim t(7, 2) As String
  112. t(1, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU0100 Cap Gauge.xls"
  113. t(1, 2) = "Lion FTT FU0100 Cap Gauge.xls"
  114. t(2, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU0240 Short Block.xls"
  115. t(2, 2) = "Lion FTT FU0240 Short Block.xls"
  116. t(3, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU0260 Squish Height.xls"
  117. t(3, 2) = "Lion FTT FU0260 Squish Height.xls"
  118. t(4, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU0860 Long Block.xls"
  119. t(4, 2) = "Lion FTT FU0860 Long Block.xls"
  120. t(5, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU0980 Oil Cavity.xls"
  121. t(5, 2) = "Lion FTT FU0980 Oil Cavity.xls"
  122. t(6, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU1420 Coolant Cavity.xls"
  123. t(6, 2) = "Lion FTT FU1420 Coolant Cavity.xls"
  124. t(7, 1) = "\\Dal00002\proj\EU5 Launch Team\Lion EU5\03 - Quality Documentation\Assembly Quality\Lion FTT Sheets\Lion FTT\Lion FTT FU1510 Cold Test.xls"
  125. t(7, 2) = "Lion FTT FU1510 Cold Test.xls"
  126. existe = False
  127. For i = 1 To 7
  128.     Workbooks.Open FileName:=t(i, 1), Notify:=False, ReadOnly:=True
  129.     nbsheet = Workbooks(t(i, 2)).Sheets.Count
  130.     For ii = 1 To nbsheet
  131.         If Sheets(ii).Name = MonthSheet Then
  132.         existe1 = True
  133.         GoTo suite10
  134.         End If
  135.     Next ii
  136. suite10:
  137.     Workbooks(t(i, 2)).Close SaveChanges:=False
  138. Next i
  139. If existe1 = False Then
  140.     MsgBox ("Please fill in in all files, the Month sheet!!" )
  141.     Exit Sub
  142. Else
  143. fisrtline = 10
  144. Sheets(Data).Select
  145. For i = 1 To 7
  146.     Workbooks.Open FileName:=t(i, 1), Notify:=False, ReadOnly:=True
  147.     Sheets(MonthSheet).Select
  148.         For ii = 22 To 33
  149.             If Cells(20, ii) = MonthFormat Then
  150.             Idcol = ii
  151.             GoTo suite11
  152.             End If
  153.         Next ii
  154. suite11:
  155.     ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(21, Idcol)
  156.     ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 6) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(22, Idcol)
  157.     ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 7) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(23, Idcol)
  158.    
  159.     ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 1, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(28, 16)
  160.     ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 2, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(29, 16)
  161.     ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 3, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(30, 16)
  162.     ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 4, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(31, 16)
  163.     ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 5, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(32, 16)
  164.    
  165.    
  166.    
  167.     'While (Workbooks(t(i, 2)).Sheets("Month" + WeekSheet).Cells(SRowFaultStart + IndexRow, SColFault) <> "" And IndexRow < 5)
  168.     'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexColTot).Value = "LB - " & Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColFault)
  169.     'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexCol1st).Value = Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColNumber)
  170.     'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexColAll).Value = Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColPercent)
  171.     'IndexRow = IndexRow + 1
  172.     'Wend
  173.    
  174.    
  175.    
  176.    
  177. Workbooks(t(i, 2)).Close SaveChanges:=False
  178. Next i
  179.     '-----------------------------------------------------------------------------------------------------'
  180.     ''' Sorting Lion Issues '''
  181.     Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexColTot) & DRowCGStart & ":" & ColLetter_from_ColNumber(DIndexColAll) & DRowCT2Start + 5).Sort Key1:=Sheets("Data" ).Cells(DRowCGStart, DIndexColAll), Order1:=xlDescending, Orientation:=xlSortColumns
  182.     '-----------------------------------------------------------------------------------------------------'
  183.     ''' Copy Top 5 Issues '''
  184.     ' Unmerge and clearcontents
  185.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).UnMerge
  186.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).ClearContents
  187.     ' Merge and border lines
  188.     For IndexFor = LRowTopStart To LRowTopStart + 4
  189.         Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekFault + 3) & IndexFor).Merge
  190.         Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekNumber) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekNumber + 1) & IndexFor).Merge
  191.         Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekPercent) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekPercent + 1) & IndexFor).Merge
  192.     Next IndexFor
  193.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).Borders(xlDiagonalDown).LineStyle = xlNone
  194.     ' Fill the table
  195.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart + 4).Value = Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexColTot) & DRowCGStart & ":" & ColLetter_from_ColNumber(DIndexColTot) & DRowCGStart + 4).Value
  196.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekNumber) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekNumber) & LRowTopStart + 4).Value = Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexCol1st) & DRowCGStart & ":" & ColLetter_from_ColNumber(DIndexCol1st) & DRowCGStart + 4).Value
  197.     Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).Value = Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexColAll) & DRowCGStart & ":" & ColLetter_from_ColNumber(DIndexColAll) & DRowCGStart + 4).Value
  198.     '-----------------------------------------------------------------------------------------------------'
  199.     ''' Shift Data '''
  200.    
  201.     ' Delete borders Month Header '
  202.     With Range(Cells(LRowStart, LColStart), Cells(LRowStart, LColEnd))
  203.         .Borders(xlInsideVertical).LineStyle = xlNone
  204.     End With
  205.    
  206.     ' Delete Month borders in the last column '
  207.     With Range(Cells(41, LColStart), Cells(LRowEnd, LColEnd))
  208.         .Borders(xlInsideHorizontal).LineStyle = xlNone
  209.         .Borders(xlInsideVertical).LineStyle = xlNone
  210.         .Borders(xlRight).LineStyle = xlNone
  211.         .Borders(xlBottom).LineStyle = xlNone
  212.     End With
  213.    
  214.     ' Shift left old data '
  215.     For IndexCol = LColStart To LColEnd
  216.         For IndexRow = LRowStart To LRowEnd
  217.             Cells(IndexRow, IndexCol).Value = Cells(IndexRow, IndexCol + 1).Value
  218.         Next IndexRow
  219.     Next IndexCol
  220.    
  221.     ' Right Border for month in the Month Header '
  222.     For IndexCol = LColStart To LColEnd
  223.         If Cells(LRowStart, IndexCol) <> "" Then
  224.             Cells(LRowStart, IndexCol).Borders(xlRight).LineStyle = xlContinuous
  225.         End If
  226.     Next IndexCol
  227.    
  228.     ' If new month then Month borders '
  229.     For IndexCol = LColStart To LColEnd
  230.         If Cells(41, IndexCol) <> "" Then
  231.             With Range(Cells(41, IndexCol), Cells(LRowEnd, IndexCol))
  232.                 .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  233.                 .Borders(xlRight).LineStyle = xlContinuous
  234.                 .Borders(xlLeft).LineStyle = xlContinuous
  235.                 .Borders(xlTop).LineStyle = xlContinuous
  236.                 .Borders(xlBottom).LineStyle = xlContinuous
  237.             End With
  238.         End If
  239.     Next IndexCol
  240.                  
  241.     '-----------------------------------------------------------------------------------------------------'
  242.     ''' Turn on the screen updating '''
  243.     Application.ScreenUpdating = True
  244.    
  245.     '''
  246.     MsgBox "Data updated"
  247.    
  248. End Sub


Message édité par fufu33 le 04-09-2008 à 15:38:46
Reply

Marsh Posté le 04-09-2008 à 11:02:21   

Reply

Marsh Posté le 04-09-2008 à 12:18:38    

Si le debug affiche l'erreur "variable not defined" avec cette ligne la surlignée, ajoute au debut de ta macro, dans ta partie definition "Dim existe as boolean"


---------------
Si Le Travail C'est La Santé, Donnez Le Mien A Quelqu'un De Malade
Reply

Marsh Posté le 04-09-2008 à 14:41:30    

Merci pour ta reponse l'erreur a ete supprime. Maintenant j'ai une erreur de type mismatch qui apparait et qui me montre le i juste en dessous de l'erreur en rouge.
Qu'est ce que je peux faire?
Merci d'avance.

Reply

Marsh Posté le 04-09-2008 à 15:16:19    

Le probleme de l'erreur mismatch, c'est qu'elle veut tout et rien dire a la fois quoi xD
Essaye de remplacer ton double i comme variable dans

Code :
  1. For ii = 1 To nbsheet
  2.         If Sheets(ii).Name = MonthSheet Then
  3.         existe1 = True
  4.         GoTo suite10
  5.         End If
  6.     Next ii


et dans

Code :
  1. For ii = 22 To 33
  2.             If Cells(20, ii) = MonthFormat Then
  3.             Idcol = ii
  4.             GoTo suite11
  5.             End If
  6.         Next ii


Je pense que vba gere mal d'avoir une variable i et une variable ii (bien que je n'en ai aucune certitude...)
Enleve aussi le "as string" de "Dim t(7, 2) As String" (on sait jamais, quand moi j'utilise des tables de valeurs, je ne definis pas le type de données a l'interieur, et cela fonctionne tres bien)


---------------
Si Le Travail C'est La Santé, Donnez Le Mien A Quelqu'un De Malade
Reply

Marsh Posté le 04-09-2008 à 15:38:10    

oui c'est exacte, il a fallu que je declare des variables a la place de mes i et sa fonctionnne. Merci beaucoup.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed