Dim maFeuille As Object For Each maFeuille In Sheets Debug.Print maFeuille.Name Next(*) Au fait, merci d'utiliser la coloration syntaxique quand tu colles du code, sinon, l'indentation disparait et, sans couleur, c'est chiant (et décourageant) à lire.
Est-ce que ce que tu cherches à faire est de demander à Range de retrouver seul la cellule qui renferme le texte "01/2010" ?
Non, ce n'est pas possible.
If Cell.Value Like "[0-9][0-9]/[0-9][0-9][0-9][0-9]" Then- un récorticage pour récupérer le numéro du mois (Left) si besoin
For Each OtherCell In Range(Cell.Address & ":A32768") If OtherCell.Value = "Total Definite" Then ' ce que tu veux faire (ton copier/coller) Exit For ' ressort de la boucle End If Next- puis tu reprends la suite de la première boucle (Next)
Sub cecile() Sheets("Def").Select For Each cell In Range("A:A") If cell.Value Like "[0-9][0-9]/[0-9][0-9][0-9][0-9]" Then If Left(cell.Value, 2) = "01" Then cell.Select Exit For End If End If For Each othercell In Range(cell.Address & ":A500") If othercell.Value = "TOTAL Definite" Then cell.Select ActiveCell.Offset(0, 6).Select Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.Copy Sheets("Jan 10").Select Range("C24").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Def").Select Exit For End If Next Next Sheets("Def").Select For Each cell In Range("a:a") If cell.Value Like "[0-9][0-9]/[0-9][0-9][0-9][0-9]" Then If Left(cell.Value, 2) = "02" Then cell.Select End If End If Exit For Next For Each othercell In Range(cell.Address & ":A500") If othercell.Value = "TOTAL Definite" Then cell.Select ActiveCell.Offset(0, 6).Select Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.Copy Sheets("Fev 10").Select Range("C24").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Def").Select End If Exit For Next Sheets("Def").Select End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub Cecile() Dim I As Long, J As Long, K As Long I = TrouveDate("01/2010") If I > 0 Then J = TrouveTotal(I) With Worksheets("Def").Range("A" & J + I) K = .Offset(0, 6).End(xlToRight).Column - 7 Worksheets("Jan 10").Range(Worksheets("Jan 10").Range("C24"), _ Worksheets("Jan 10").Range("C24").Offset(0, K)).Value _ = Worksheets("Def").Range(.Offset(0, 6), .Offset(0, 6 + K)).Value End With End If I = TrouveDate("02/2010") If I > 0 Then J = TrouveTotal(I) With Worksheets("Def").Range("A" & J + I) K = .Offset(0, 6).End(xlToRight).Column - 7 Worksheets("Fev 10").Range(Worksheets("Fev 10").Range("C24"), _ Worksheets("Fev 10").Range("C24").Offset(0, K)).Value _ = Worksheets("Def").Range(.Offset(0, 6), .Offset(0, 6 + K)).Value End With End If End Sub Function TrouveDate(laDate As String) As Long TrouveDate = Application.Evaluate("MATCH(""" & laDate & """,Def!A:A,0)") End Function Function TrouveTotal(Depart As Long) As Long TrouveTotal = Application.Evaluate("MATCH(""Total Definite"",Def!A" & _ Depart & ":A" & Worksheets("Def").Rows.Count & ",0)") - 1 End Function