fufu33 | 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 :
- Option Explicit
- Sub PickUpdataMonth(WeekNum, WeekYear, MonthSheet, MonthFormat)
- ''' Index '''
- Dim IndexFor As Integer
- Dim IndexRow As Integer
- Dim IndexCol As Integer
- ''' Constants Stations Files '''
- Dim SRowFaultStart As Integer
- Dim SRangeTot As String
- Dim SRange1st As String
- Dim SRangeAll As String
- Dim SColFault As Integer
- Dim SColNumber As Integer
- Dim SColPercent As Integer
- Dim SColFault2 As Integer
- Dim SColNumber2 As Integer
- Dim SColPercent2 As Integer
- ''' Constants Overall File Data sheet '''
- Dim DRowWeek As Integer
- Dim DRowHeader As Integer
- Dim DRowCG As Integer
- Dim DRowSB As Integer
- Dim DRowSH As Integer
- Dim DRowLB As Integer
- Dim DRowOC As Integer
- Dim DRowCC As Integer
- Dim DRowCT As Integer
- Dim DRowCGStart As Integer
- Dim DRowSBStart As Integer
- Dim DRowSHStart As Integer
- Dim DRowLBStart As Integer
- Dim DRowOC1Start As Integer
- Dim DRowOC2Start As Integer
- Dim DRowCCStart As Integer
- Dim DRowCT1Start As Integer
- Dim DRowCT2Start As Integer
- ''' Constants Overall File Lion sheet '''
- Dim LRowTopStart As Integer
- Dim LColWeekFault As Integer
- Dim LColWeekNumber As Integer
- Dim LColWeekPercent As Integer
- Dim LColMonthFault As Integer
- Dim LColMonthNumber As Integer
- Dim LColMonthPercent As Integer
- Dim LColStart As Integer
- Dim LColEnd As Integer
- Dim LRowStart As Integer
- Dim LRowEnd As Integer
- ''' Index '''
- Dim DIndexColTot As Integer
- Dim DIndexCol1st As Integer
- Dim DIndexColAll As Integer
- '-----------------------------------------------------------------------------------------------------'
- ''' Init Constants Stations Files '''
- SRowFaultStart = 5
- SRangeTot = "L5"
- SRange1st = "L6"
- SRangeAll = "L7"
- SColFault = 1
- SColNumber = 2
- SColPercent = 3
- SColFault2 = 5
- SColNumber2 = 6
- SColPercent2 = 7
-
- ''' Init Constants Overall File Data Sheet '''
- DRowWeek = 1
- DRowHeader = 2
- DRowCG = 3
- DRowSB = 4
- DRowSH = 5
- DRowLB = 6
- DRowOC = 7
- DRowCC = 8
- DRowCT = 9
- DRowCGStart = 10
- DRowSBStart = 15
- DRowSHStart = 20
- DRowLBStart = 25
- DRowOC1Start = 30
- DRowOC2Start = 35
- DRowCCStart = 40
- DRowCT1Start = 45
- DRowCT2Start = 50
-
- ''' Constants Overall File Lion sheet '''
- LRowTopStart = 51
- LColWeekFault = 1
- LColWeekNumber = 5
- LColWeekPercent = 7
- LColMonthFault = 13
- LColMonthNumber = 17
- LColMonthPercent = 19
- LColStart = 5
- LColEnd = 19
- LRowStart = 31
- LRowEnd = 48
-
-
- ''' Init '''
- DIndexColTot = 2
- DIndexCol1st = 3
- DIndexColAll = 4
-
- '-----------------------------------------------------------------------------------------------------'
- ''' Turn off the screen updating '''
- Application.ScreenUpdating = False
- '-----------------------------------------------------------------------------------------------------'
- ''' ClearContents old data in sheet "Data" '''
- Sheets("Data" ).Range(ColLetter_from_ColNumber(DIndexColTot) & DRowCG & ":" & ColLetter_from_ColNumber(DIndexColAll) & DRowCT2Start + 4).ClearContents
- Dim t(7, 2) As String
- 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"
- t(1, 2) = "Lion FTT FU0100 Cap Gauge.xls"
- 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"
- t(2, 2) = "Lion FTT FU0240 Short Block.xls"
- 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"
- t(3, 2) = "Lion FTT FU0260 Squish Height.xls"
- 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"
- t(4, 2) = "Lion FTT FU0860 Long Block.xls"
- 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"
- t(5, 2) = "Lion FTT FU0980 Oil Cavity.xls"
- 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"
- t(6, 2) = "Lion FTT FU1420 Coolant Cavity.xls"
- 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"
- t(7, 2) = "Lion FTT FU1510 Cold Test.xls"
- existe = False
- For i = 1 To 7
- Workbooks.Open FileName:=t(i, 1), Notify:=False, ReadOnly:=True
- nbsheet = Workbooks(t(i, 2)).Sheets.Count
- For ii = 1 To nbsheet
- If Sheets(ii).Name = MonthSheet Then
- existe1 = True
- GoTo suite10
- End If
- Next ii
- suite10:
- Workbooks(t(i, 2)).Close SaveChanges:=False
- Next i
- If existe1 = False Then
- MsgBox ("Please fill in in all files, the Month sheet!!" )
- Exit Sub
- Else
- fisrtline = 10
- Sheets(Data).Select
- For i = 1 To 7
- Workbooks.Open FileName:=t(i, 1), Notify:=False, ReadOnly:=True
- Sheets(MonthSheet).Select
- For ii = 22 To 33
- If Cells(20, ii) = MonthFormat Then
- Idcol = ii
- GoTo suite11
- End If
- Next ii
- suite11:
- ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(21, Idcol)
- ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 6) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(22, Idcol)
- ActiveWorkbook.Sheets("Data" ).Cells(i + 2, 7) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(23, Idcol)
-
- ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 1, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(28, 16)
- ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 2, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(29, 16)
- ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 3, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(30, 16)
- ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 4, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(31, 16)
- ActiveWorkbook.Sheets("Data" ).Cells(9 + (i - 1) * 5 + 5, 5) = Workbooks(t(i, 2)).Sheets(MonthSheet).Cells(32, 16)
-
-
-
- 'While (Workbooks(t(i, 2)).Sheets("Month" + WeekSheet).Cells(SRowFaultStart + IndexRow, SColFault) <> "" And IndexRow < 5)
- 'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexColTot).Value = "LB - " & Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColFault)
- 'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexCol1st).Value = Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColNumber)
- 'ActiveWorkbook.Sheets("Data" ).Cells(DRowLBStart + IndexRow, DIndexColAll).Value = Workbooks(t(i, 2)).Sheets("Month " + WeekSheet).Cells(SRowFaultStart + IndexRow, SColPercent)
- 'IndexRow = IndexRow + 1
- 'Wend
-
-
-
-
- Workbooks(t(i, 2)).Close SaveChanges:=False
- Next i
- '-----------------------------------------------------------------------------------------------------'
- ''' Sorting Lion Issues '''
- 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
- '-----------------------------------------------------------------------------------------------------'
- ''' Copy Top 5 Issues '''
- ' Unmerge and clearcontents
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).UnMerge
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).ClearContents
- ' Merge and border lines
- For IndexFor = LRowTopStart To LRowTopStart + 4
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekFault + 3) & IndexFor).Merge
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekNumber) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekNumber + 1) & IndexFor).Merge
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekPercent) & IndexFor & ":" & ColLetter_from_ColNumber(LColWeekPercent + 1) & IndexFor).Merge
- Next IndexFor
- Sheets("Lion FTT" ).Range(ColLetter_from_ColNumber(LColWeekFault) & LRowTopStart & ":" & ColLetter_from_ColNumber(LColWeekPercent) & LRowTopStart + 4).Borders(xlDiagonalDown).LineStyle = xlNone
- ' Fill the table
- 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
- 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
- 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
- '-----------------------------------------------------------------------------------------------------'
- ''' Shift Data '''
-
- ' Delete borders Month Header '
- With Range(Cells(LRowStart, LColStart), Cells(LRowStart, LColEnd))
- .Borders(xlInsideVertical).LineStyle = xlNone
- End With
-
- ' Delete Month borders in the last column '
- With Range(Cells(41, LColStart), Cells(LRowEnd, LColEnd))
- .Borders(xlInsideHorizontal).LineStyle = xlNone
- .Borders(xlInsideVertical).LineStyle = xlNone
- .Borders(xlRight).LineStyle = xlNone
- .Borders(xlBottom).LineStyle = xlNone
- End With
-
- ' Shift left old data '
- For IndexCol = LColStart To LColEnd
- For IndexRow = LRowStart To LRowEnd
- Cells(IndexRow, IndexCol).Value = Cells(IndexRow, IndexCol + 1).Value
- Next IndexRow
- Next IndexCol
-
- ' Right Border for month in the Month Header '
- For IndexCol = LColStart To LColEnd
- If Cells(LRowStart, IndexCol) <> "" Then
- Cells(LRowStart, IndexCol).Borders(xlRight).LineStyle = xlContinuous
- End If
- Next IndexCol
-
- ' If new month then Month borders '
- For IndexCol = LColStart To LColEnd
- If Cells(41, IndexCol) <> "" Then
- With Range(Cells(41, IndexCol), Cells(LRowEnd, IndexCol))
- .Borders(xlInsideHorizontal).LineStyle = xlContinuous
- .Borders(xlRight).LineStyle = xlContinuous
- .Borders(xlLeft).LineStyle = xlContinuous
- .Borders(xlTop).LineStyle = xlContinuous
- .Borders(xlBottom).LineStyle = xlContinuous
- End With
- End If
- Next IndexCol
-
- '-----------------------------------------------------------------------------------------------------'
- ''' Turn on the screen updating '''
- Application.ScreenUpdating = True
-
- '''
- MsgBox "Data updated"
-
- End Sub
|
Message édité par fufu33 le 04-09-2008 à 15:38:46
|