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