Boucle FOR

[akramenergie]

Bonjour le forums,
Je voudrais transformer une dixéne de ligne de code qui se répete en une boucle For.

j'ai indiqué en rouge les deux variable qui change la premiere est un chifre qui s'incrémente 1 par 1 et la deuxiéme variable c'est une lettre qui s'incrémente 3 par 3

Sheets("2010").Cells(l + 6, 4).Value = Evaluate("SumProduct((F7:F" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 5).Value = Evaluate("SumProduct((I7:I" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 6).Value = Evaluate("SumProduct((L7:L" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 7).Value = Evaluate("SumProduct((O7:O" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 8).Value = Evaluate("SumProduct((R7:R" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 9).Value = Evaluate("SumProduct((U7:U" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 10).Value = Evaluate("SumProduct((X7:X" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 11).Value = Evaluate("SumProduct((AA7:AA" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 12).Value = Evaluate("SumProduct((AD7:AD" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 13).Value = Evaluate("SumProduct((AG7:AG" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 14).Value = Evaluate("SumProduct((AJ7:AJ" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 15).Value = Evaluate("SumProduct((AM7:AM" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 16).Value = Evaluate("SumProduct((AP7:AP" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 17).Value = Evaluate("SumProduct((AS7:AS" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 18).Value = Evaluate("SumProduct((AV7:AV" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 19).Value = Evaluate("SumProduct((AY7:AY" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 20).Value = Evaluate("SumProduct((BB7:BB" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 21).Value = Evaluate("SumProduct((BE7:BE" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 22).Value = Evaluate("SumProduct((BH7:BH" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 23).Value = Evaluate("SumProduct((BK7:BK" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 24).Value = Evaluate("SumProduct((BN7:BN" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 25).Value = Evaluate("SumProduct((BQ7:BQ" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
Sheets("2010").Cells(l + 6, 26).Value = Evaluate("SumProduct((BT7:BT" & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala

 

[pierrejean]

Re : Boucle FOR

Bonjour akramenergie

A tester

Sub test()
For n = 4 To 26
 lalettre = 6
 Sheets("2010").Cells(l + 6, 4).Value = Evaluate("SumProduct((" & lettre(lalettre) & "7:" & lettre(lalettre) & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
 lalettre = lalettre + 3
Next
End Sub
Function lettre(col)
lettre = Replace(Cells(1, col).Address(0, 0), "1", "")
End Function

 

[Compte Supprimé 979]

Re : Boucle FOR

Salut Akramenergie
Bonjour PierreJean

Comme j'avais fait quelque chose également, je mets mon code 😉

Sub test()
  Dim Col, Lig As Long, LCol As String, Adr As String
  Col = 6
  For Lig = 4 To 26
    Adr = Replace(Cells(Lig, Col).Address, "$", "")
    LCol = Left(Adr, Len(Adr) - 1)
    Sheets("2010").Cells(l + 6, Lig).Value = Evaluate("SumProduct((" & LCol & "7:" & LCol & l & ")*(B7:B" & l & "=C" & l + 6 & "))") / Vala
  Col = Col + 3
  Next
End Sub

Merci de mettre le code entre balise [ Code] et [/ Code]
(en supprimant les espaces)

A+

 

[akramenergie]

Re : Boucle FOR

Merci pierrejean pour ta réponsse.

Mais je vois pas ou "n" de l aboucle for intérvien dans le code que tu ma écrit 😕

 

[pierrejean]

Re : Boucle FOR

Re
OK voila :

Sub test()
For n = 4 To 26
 lalettre = 6
 Sheets("2010").Cells(l + 6, [COLOR=red]n[/COLOR]).Value = Evaluate("SumProduct((" & lettre(lalettre) & "7:" & lettre(lalettre) & l & ") * (B7:B" & l & " = C" & l + 6 & "))") / vala
 lalettre = lalettre + 3
Next
End Sub
Function lettre(col)
lettre = Replace(Cells(1, col).Address(0, 0), "1", "")
End Function