Discussion :

[tomy7]

bonjour,
je cherche a recuperer le numero d une ligne que je viens d ajouter et a le mettre ds une variable j ai essayer ce genre de code et je v passer en vert ou cela me semble incoherent avec ma demande...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Private Sub CommandButton25_Click()
    Dim NewSh As Worksheet
    Dim Ligne As Long

    Worksheets("Add").Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
    Set NewSh = ActiveWorkbook.Worksheets(Sheets.Count)
    NewSh.Name = "Add" & Sheets.Count - 3
    NewSh.visible = True
    Line = Sheets("Main").Cells(Rows.Count, "G").End(xlUp).Row + 1
    Sheets("Main").Rows(Line).Insert xlShiftDown
    Worksheets("Main").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(5, "C").Address
    Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(4, "J").Address
    Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "F").Address
    Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "Q").Address
    Worksheets("Main").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "R").Address
    i = Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal
    j = Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal
    Select Case True
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "AC"
        Worksheets("Main").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).FormulaLocal = "=L277 + I" & i
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "EAC"
        Worksheets("Main").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).FormulaLocal = "=M277 + J" & j
    End Select
    Worksheets("Main").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).FormulaLocal = "=1-(M" & j & "/L" & i & ")"
    Worksheets("Main").Select
End Sub

je desirai que i et j soit le num de ma ligne que je viens d ajouter a mon tableau.

une idée?

merci

[tomy7]

ceci devient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Private Sub CommandButton25_Click()
    Dim NewSh As Worksheet
    Dim Ligne As Long
 
    Worksheets("Add").Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
    Set NewSh = ActiveWorkbook.Worksheets(Sheets.Count)
    NewSh.Name = "Add" & Sheets.Count - 3
    NewSh.visible = True
    Line = Sheets("Main").Cells(Rows.Count, "G").End(xlUp).Row + 1
    Sheets("Main").Rows(Line).Insert xlShiftDown
    Worksheets("Main").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(5, "C").Address
    Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(4, "J").Address
    Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "F").Address
    Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "Q").Address
    Worksheets("Main").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "R").Address
    i = Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal
    j = Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal
    Select Case True
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "AC"
        Worksheets("Main").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).FormulaLocal = "=L277 + I" & i
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "EAC"
        Worksheets("Main").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).FormulaLocal = "=M277 + J" & j
    End Select
    Worksheets("Main").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).FormulaLocal = "=1-(M" & j & "/L" & i & ")"
    Worksheets("Main").Select
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Private Sub CommandButton25_Click()
    Dim NewSh As Worksheet
    Dim Ligne As Long
 
    Worksheets("Add").Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
    Set NewSh = ActiveWorkbook.Worksheets(Sheets.Count)
    NewSh.Name = "Add" & Sheets.Count - 3
    NewSh.visible = True
    Line = Sheets("Main").Cells(Rows.Count, "G").End(xlUp).Row + 1
    Sheets("Main").Rows(Line).Insert xlShiftDown
    Worksheets("Main").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(5, "C").Address
    Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(4, "J").Address
    Worksheets("Main").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "F").Address
    Worksheets("Main").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "Q").Address
    Worksheets("Main").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).FormulaLocal = "=" & NewSh.Name & "!" & NewSh.Cells(266, "R").Address
       Select Case True
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "AC"
        Worksheets("Main").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).FormulaLocal = "=L277 + I" & Line
    Case Worksheets("Main").Cells(Rows.Count, "H").End(xlUp).Value = "EAC"
        Worksheets("Main").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).FormulaLocal = "=M277 + J" & Line
    End Select
    Worksheets("Main").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).FormulaLocal = "=1-(M" & Line & "/L" & Line & ")"
    Worksheets("Main").Select
End Sub

je me fais les questions et les reponses , DSL