Option Explicit
Sub Macro1()
'*************************
'déclaration des variables
'*************************
Dim Dest_well_cDNA As Integer
Dim Source_BC_pos_primer As Integer
Dim Dest_well_Primers As Integer
Dim row2 As Integer
Dim row3 As Variant 'je ne comprends pas à quoi sert cette variable ?
Dim li As Integer
Dim col As Byte
Dim cel As Range
Dim cdnanumb As Integer
Dim primernumb As Integer
Dim nombredecases As Long
Dim reporter As String
Dim vol_CDNA As Double
Dim Vol_primer_mix As Double
Dim Source_pos_cDNA As String
Dim Source_BC_primer As String
Dim Dest_pos_cDNA As String
Dim i As Integer
Dim CDNA_name As String
Dim j As Integer
Dim primer_name As String
Dim CDNAPRIMER As String
Dim L As Long
Application.ScreenUpdating = False
Sheets("CDNA_PRIMER").Range("A2:A200").ClearContents
'*************************************
'initialisation de certaines variables
'*************************************
Dest_well_cDNA = 1
Source_BC_pos_primer = 1
Dest_well_Primers = 1
row2 = 2
row3 = 1 'initilisation à : 1 ?
li = 2 'pour eviter le décalage
col = 1
'***********************************
'récupération de certaines variables
'***********************************
With Sheets("CDNA_PRIMER")
For Each cel In .Range("G22:R29")
If cel.Value <> "" Then
.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = cel.Value
.Cells(Application.Rows.Count, 1).End(xlUp).AddComment 'ajoute un commentaire
.Cells(Application.Rows.Count, 1).End(xlUp).Comment.Text Text:=.Range("H19").Value 'texte du commentaire
.Cells(Application.Rows.Count, 1).End(xlUp).Comment.Visible = False
End If
Next cel
cdnanumb = .Cells(Application.Rows.Count, 1).End(xlUp).Row - 1
primernumb = .Cells(Application.Rows.Count, 2).End(xlUp).Row - 1
nombredecases = cdnanumb * primernumb
reporter = .Range("I4").Value
vol_CDNA = .Range("I1").Value
Vol_primer_mix = .Range("I2").Value
Source_pos_cDNA = .Range("H19").Value
For Each cel In .Range("M14:R17")
If cel.Value <> "" Then
.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = cel.Value
.Cells(Application.Rows.Count, 1).End(xlUp).AddComment 'ajoute un commentaire
.Cells(Application.Rows.Count, 1).End(xlUp).Comment.Text Text:=.Range("O12").Value 'texte du commentaire
.Cells(Application.Rows.Count, 1).End(xlUp).Comment.Visible = False
End If
Next cel
cdnanumb = .Cells(Application.Rows.Count, 1).End(xlUp).Row - 1
Source_BC_primer = .Range("H12").Value
Dest_pos_cDNA = .Range("I3").Value
End With
'*********************************
'condition de continuation du code
'*********************************
If nombredecases > 128 Then
MsgBox ("ERROR there are too many samples/ Primer")
GoTo LAFIN
End If
'*****************************************************
'effacement des anciennes données placement des titres
'*****************************************************
With Sheets("layout")
.Cells.ClearContents
.Range("A1:X1").Merge
.Range("A1:X1").Value = "cDNA+Primers"
.Columns("A:X").EntireColumn.AutoFit
End With
With Sheets("CDNA_layout")
.Cells.ClearContents
.Range("A1:X1").Merge
.Range("A1:X1").Value = "cDNA Plate"
End With
With Sheets("Primer_layout")
.Cells.ClearContents
.Range("A1:X1").Merge
.Range("A1:X1").Value = "Primer Plate"
End With
With Sheets("bilan")
.Cells.ClearContents
.Range("A1").Value = "Source_pos_cDNA"
.Range("B1").Value = "CDNA_name"
.Range("C1").Value = "Source_cDNA_Well"
.Range("D1").Value = "vol_cDNA"
.Range("E1").Value = "Dest_pos_cDNA"
.Range("F1").Value = "Dest_well_cDNA"
.Range("G1").Value = "Source_BC_primer"
.Range("H1").Value = "primer_name"
.Range("I1").Value = "Source_BC_pos_primer"
.Range("J1").Value = "Vol_primer_mix"
.Range("K1").Value = "Dest_well_Primers"
End With
With Sheets("SDS_file")
.Cells.ClearContents
.Range("A1").Value = "*** SDS Setup File Version"
.Range("A2").Value = "*** Output Plate Size"
.Range("A3").Value = "*** Output Plate ID"
.Range("A4").Value = "*** Number of Detectors"
.Range("B1").Value = "3"
.Range("B2").Value = "384"
.Range("B3").Value = Sheets("CDNA_Primer").Range("I5")
.Range("B4").Value = primernumb
.Range("A5").Value = "Detector"
.Range("B5").Value = "Reporter"
.Range("C5").Value = "Quencher"
.Range("D5").Value = "Description"
.Range("E5").Value = "Comments"
Sheets("CDNA_PRIMER").Range("B2:B" & Sheets("CDNA_PRIMER").Cells(Application.Rows.Count, 2).End(xlUp).Row).Copy
.Range("A6").PasteSpecial xlPasteValues
Application.CutCopyMode = False
For Each cel In .Range("A6:A" & .Cells(Application.Rows.Count, 1).End(xlUp).Row)
cel.Offset(0, 1) = Sheets("CDNA_PRIMER").Range("I4")
Next
End With
'*************************************************
'placement des données dans les différents onglets
'*************************************************
For i = 2 To cdnanumb + 1
CDNA_name = Sheets("CDNA_PRIMER").Cells(i, 1).Value
Source_pos_cDNA = Sheets("CDNA_PRIMER").Cells(i, 1).Comment.Text 'récupère le commentaire
Sheets("CDNA_PRIMER").Cells(i, 1).ClearComments 'efface le commentaire
For j = 2 To primernumb + 1
primer_name = Sheets("CDNA_PRIMER").Cells(j, 2).Value
CDNAPRIMER = CDNA_name & "_" & primer_name
For L = 1 To 3
With Sheets("layout")
.Cells(li, col).Value = CDNAPRIMER
End With
With Sheets("CDNA_layout")
.Cells(li, col).Value = CDNA_name
End With
With Sheets("Primer_layout")
.Cells(li, col).Value = primer_name
End With
With Sheets("bilan")
.Cells(row2, 1).Value = Source_pos_cDNA
.Cells(row2, 2).Value = CDNA_name
.Cells(row2, 3).Value = bilan_cDNA_well(Cells(row2, 2))
.Cells(row2, 4).Value = vol_CDNA
.Cells(row2, 5).Value = Dest_pos_cDNA
.Cells(row2, 6).Value = Dest_well_cDNA
.Cells(row2, 7).Value = Source_BC_primer
.Cells(row2, 8).Value = primer_name
.Cells(row2, 9).Value = bilan_primer_pos(Cells(row2, 8))
.Cells(row2, 10).Value = Vol_primer_mix
.Cells(row2, 11).Value = Dest_well_Primers
.Cells(row2, 12).Value = controls(Cells(row2, 2))
If .Cells(row2, 12).Value <> "" Then .Cells(row2, 3).Value = .Cells(row2, 12).Value
End With
'****************************
'incrémentation des variables
'****************************
Source_BC_pos_primer = Source_BC_pos_primer + 1
Dest_well_cDNA = Dest_well_cDNA + 1
Dest_well_Primers = Dest_well_Primers + 1
col = col + 1
If col > 24 Then
col = 1
li = li + 1
End If
row2 = row2 + 1
row3 = Sheets("CDNA_PRIMER").Range("B4").Value 'pourquoi, alors qu'elle a été initialisée à : 1 ?
Next L
Next j
Next i
'Source_cDNA_Well = Source_cDNA_Well + 12
'IF Source_cDNA_Well = 97 Then Source_cDNA_Well = 2
'IF Source_cDNA_Well = 98 Then Source_cDNA_Well = 3
'IF Source_cDNA_Well = 99 Then Source_cDNA_Well = 4
'IF Source_cDNA_Well = 100 Then Source_cDNA_Well = 5
'IF Source_cDNA_Well = 101 Then Source_cDNA_Well = 6
'IF Source_cDNA_Well = 102 Then Source_cDNA_Well = 7
'IF Source_cDNA_Well = 103 Then Source_cDNA_Well = 8
'IF Source_cDNA_Well = 104 Then Source_cDNA_Well = 9
'IF Source_cDNA_Well = 105 Then Source_cDNA_Well = 10
'IF Source_cDNA_Well = 106 Then Source_cDNA_Well = 11
'IF Source_cDNA_Well = 107 Then Source_cDNA_Well = 12
Application.ScreenUpdating = True
Sheets("SDS_file").Select
LAFIN:
Application.Run "part2"
End Sub
Sub part2()
Dim Ligne As Long
Sheets("SDS_file").Select
Ligne = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(Ligne, "A").Resize(1, 5) = Array("Well", "Sample Name", "Detector", "Task", "Quantity")
Cells(Ligne + 1, "A") = "1"
Cells(Ligne + 1, "D").Resize(1, 2) = Array("UNKN", 0)
Cells(Ligne + 1, "A").AutoFill Range(Cells(Ligne + 1, "A"), Cells(Ligne + 384, "A")), Type:=xlFillSeries
Cells(Ligne + 1, "D").Resize(1, 2).AutoFill Range(Cells(Ligne + 1, "D").Resize(1, 2), Cells(Ligne + 384, "D").Resize(1, 2)), Type:=xlFillCopy
Sheets("bilan").Range("B2:B385").Copy
Sheets("SDS_file").Select
Cells(Ligne + 1, "B").PasteSpecial xlPasteValues
Sheets("bilan").Range("H2:H385").Copy
Sheets("SDS_file").Select
Cells(Ligne + 1, "C").PasteSpecial xlPasteValues
ActiveWorkbook.Save
Sheets("bilan").Select
Application.Run "export"
End Sub
Sub export()
Dim rep As String
rep = ActiveWorkbook.Path & "\"
Sheets("Bilan").Copy
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs rep & "bilan" & ".csv"
.Close True
Application.DisplayAlerts = True
End With
Sheets("SDS_file").Copy
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs rep & "SDS_file" & ".txt", xlTextMSDOS
.Close True
Application.DisplayAlerts = True
End With
End Sub