Probleme Variable not defined [RESOLU]

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : Probleme Variable not defined [RESOLU]

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

Publicité

Moonschild

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

fufu33

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.

Moonschild

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 :

For ii = 1 To nbsheet
If Sheets(ii).Name = MonthSheet Then
existe1 = True
GoTo suite10
End If
Next ii

et dans

Code :

For ii = 22 To 33
If Cells(20, ii) = MonthFormat Then
Idcol = ii
GoTo suite11
End If
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

fufu33

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

FORUM HardWare.fr

Programmation

VB/VBA/VBS

Probleme Variable not defined [RESOLU]

Sujets relatifs
probleme vb excel inter fichiers	[Résolu] Style Css Formulaire IE6
Batch : Save et load de backup (résolu)	ASP .NET : Problème avec le controle MENU
[C] sizeof et variable de structure	gestion des droits d'acces [VBA excel] [RESOLU]
[css] probleme div et attribut float !	variable PHP pour HTML
[shell] problème dans un for	[Resolu][C#, ASP.net / access] problème requete update
Plus de sujets relatifs à : Probleme Variable not defined [RESOLU]

Page générée en 0.121 secondes