Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Round([C9], 0) <> [C11] Or Intersect(Target, [D7:AD7,C11]) Is Nothing Then Exit Sub
With [B:B].Find("", [B11], xlValues) '1ère cellule vide sous B11
With .Resize(, 29)
.Value = [B9:AD9].Value
.Borders(xlEdgeBottom).Weight = xlMedium
End With
.Value = "PF " & .Row - [B11].Row
End With
End Sub
Bonjour mcj1997, san san,
Vous voulez créer les lignes quand il se passe quoi ? Vous ne dites pas à quoi servent C9 et C11 !
A+
Je ne comprends rien et puisqu'il faut vous arracher les informations au compte-gouttes j'abandonne.Ainsi autre exemple si C9 égal 259 et c11égal 50, nous aurons 5 lignes (5 portefeuilles avec 50 et 1 avec 9).
ca me rassure je ne suis pas la seule lolJe ne comprends rien et puisqu'il faut vous arracher les informations au compte-gouttes j'abandonne.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Total As Double, MaxPF As Double, NbLig As Long, RngLig As Range
If Intersect(Target, [D7:AD7,C11,D12:AD1000], Target) Is Nothing Then Exit Sub
Total = [C9].Value
MaxPF = [C11].Value
NbLig = Int(Total / MaxPF): If NbLig * MaxPF < Total Then NbLig = NbLig + 1
Set RngLig = [B12:AD12].Resize(NbLig)
Application.EnableEvents = False
With RngLig.Offset(NbLig).Resize(1000)
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.ClearContents: End With
With RngLig.Borders(xlEdgeBottom): .LineStyle = xlContinuous: .Weight = xlMedium: End With
With RngLig.Borders(xlInsideHorizontal): .LineStyle = xlContinuous: .Weight = xlThin: End With
RngLig.Columns(2).Value = MaxPF
RngLig.Rows(RngLig.Rows.Count).FormulaR1C1 = "=R9C-SUM(R12C:R[-1]C)"
RngLig.Columns(1).FormulaR1C1 = "=""PF ""&ROW()-" & RngLig.Row - 1
RngLig.Value = RngLig.Value
Application.EnableEvents = True
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Total As Double, MaxPF As Double, NbLig As Long, RngLig As Range
If Intersect(Target, [D7:AD7,C11,D12:AD1000], Target) Is Nothing Then Exit Sub
Total = [C9].Value
MaxPF = [C11].Value
NbLig = Int(Total / MaxPF): If NbLig * MaxPF < Total Then NbLig = NbLig + 1
If NbLig = 0 Then NbLig = 1
Set RngLig = [B12:AD12].Resize(NbLig)
Application.EnableEvents = False
With RngLig.Offset(NbLig).Resize(1000)
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
.ClearContents: End With
With RngLig.Borders(xlEdgeBottom): .LineStyle = xlContinuous: .Weight = xlMedium: End With
With RngLig.Borders(xlInsideHorizontal): .LineStyle = xlContinuous: .Weight = xlThin: End With
RngLig.Columns(2).Value = MaxPF
If RngLig.Rows.Count > 1 Then
RngLig.Rows(RngLig.Rows.Count).FormulaR1C1 = "=R9C-SUM(R12C:R[-1]C)"
Else
RngLig.Rows(1).FormulaR1C1 = "=R9C": End If
RngLig.Columns(1).FormulaR1C1 = "=""PF ""&ROW()-" & RngLig.Row - 1
RngLig.Value = RngLig.Value
Application.EnableEvents = True
End Sub