Hi sorry if it is a newbie question.
I have a dynamic table which start using the range Next_tableau variable :Set Next_tableau = Range("C" & Rows.Count).End(xlUp).Offset(6) and it works perfectly.
But if i want to put data 2 rows below the dynamic table using other variable Detail_calcul : Set Detail_calcul = Range("C" & Rows.Count).End(xlUp).Offset(2) it doesn't work it sets the value 4 rows above the table.
I am completly lost i have trying for hours
If someone could help i would be grateful.
Here is my code :
Private Sub CommandButton3_Click()
Dim Next_tableau As Range
Dim Detail_calcul As Range
Set Next_tableau = Range("C" & Rows.Count).End(xlUp).Offset(6)
Set Detail_calcul = Range("C" & Rows.Count).End(xlUp).Offset(2)
Dim startCell As Range
Dim CellsEmpty As Boolean
CellsEmpty = True
For Each Cell In Range("A1:D8")
If Cell.Value <> "" Then
CellsEmpty = False
Exit For
End If
Next
If CellsEmpty = True Then
Set startCell = Range("C8")
Else: Set startCell = Next_tableau
End If
Sheets("Copies").Select
Range("C6:I9").Select
Selection.Copy
Sheets("Data").Select
startCell.Select
ActiveSheet.Paste
Dim Tableau_MP As Range
Set Tableau_MP = startCell.CurrentRegion.Offset(2, 1)
'MT1
'Calcul du prix unitaire pour les stocks
If TextBox1 <> "" And TextBox3 <> "" Then
TextBox2.Value = TextBox3 / TextBox1
Detail_calcul.Value = "Prix unitaire stock" & " " & Mat1_Nom.Value
Detail_calcul.Offset(0, 1).Value = "(" & TextBox3 & "/" & TextBox1 & ")=" & Tableau_MP.Cells(1, 2)
End If
I have a dynamic table which start using the range Next_tableau variable :Set Next_tableau = Range("C" & Rows.Count).End(xlUp).Offset(6) and it works perfectly.
But if i want to put data 2 rows below the dynamic table using other variable Detail_calcul : Set Detail_calcul = Range("C" & Rows.Count).End(xlUp).Offset(2) it doesn't work it sets the value 4 rows above the table.
I am completly lost i have trying for hours
If someone could help i would be grateful.
Here is my code :
Private Sub CommandButton3_Click()
Dim Next_tableau As Range
Dim Detail_calcul As Range
Set Next_tableau = Range("C" & Rows.Count).End(xlUp).Offset(6)
Set Detail_calcul = Range("C" & Rows.Count).End(xlUp).Offset(2)
Dim startCell As Range
Dim CellsEmpty As Boolean
CellsEmpty = True
For Each Cell In Range("A1:D8")
If Cell.Value <> "" Then
CellsEmpty = False
Exit For
End If
Next
If CellsEmpty = True Then
Set startCell = Range("C8")
Else: Set startCell = Next_tableau
End If
Sheets("Copies").Select
Range("C6:I9").Select
Selection.Copy
Sheets("Data").Select
startCell.Select
ActiveSheet.Paste
Dim Tableau_MP As Range
Set Tableau_MP = startCell.CurrentRegion.Offset(2, 1)
'MT1
'Calcul du prix unitaire pour les stocks
If TextBox1 <> "" And TextBox3 <> "" Then
TextBox2.Value = TextBox3 / TextBox1
Detail_calcul.Value = "Prix unitaire stock" & " " & Mat1_Nom.Value
Detail_calcul.Offset(0, 1).Value = "(" & TextBox3 & "/" & TextBox1 & ")=" & Tableau_MP.Cells(1, 2)
End If