choupi_nette
XLDnaute Occasionnel
Bonjour,
Je voudrai savoir si l'un d'entre vous saurait comment alleger ce code?
merci
Je voudrai savoir si l'un d'entre vous saurait comment alleger ce code?
merci
Sub Macro1()
Application.ScreenUpdating = False
Sheets("CDNA_layout").Cells.ClearContents
Sheets("Primer_layout").Cells.ClearContents
Sheets("layout").Cells.ClearContents
Sheets("bilan").Cells.ClearContents
Sheets("SDS_file").Cells.ClearContents
CDNAposition = 1
compteur384 = 1
primer_pos = 1
Source_cDNA_Well = 1
Dest_well_cDNA = 1
Source_BC_pos_primer = 1
Dest_well_Primers = 1
row2 = 2
row3 = 1
Row = 1
col = 1
Sheets("CDNA_PRIMER").Select
Range("A1").End(xlDown).Select
cdnarow = ActiveCell.Row
cdnanumb = cdnarow - 1
Range("B1").End(xlDown).Select
primerrow = ActiveCell.Row
primernumb = primerrow - 1
nombredecases = cdnanumb * primernumb
Range("I4").Select
reporter = ActiveCell.Value
Range("I1").Select
vol_CDNA = ActiveCell.Value
Range("I2").Select
Vol_primer_mix = ActiveCell.Value
Range("H19").Select
Source_pos_cDNA = ActiveCell.Value
Range("H12").Select
Source_BC_primer = ActiveCell.Value
Range("I3").Select
Dest_pos_cDNA = ActiveCell.Value
If nombredecases > 128 Then
MsgBox ("ERROR there are too many samples/ Primer")
GoTo LAFIN
End If
For I = 2 To cdnanumb + 1
CDNA_name = "A" & I
Range(CDNA_name).Select
CDNA_name = ActiveCell.Value
For J = 2 To primernumb + 1
primer_name = "B" & J
Range(primer_name).Select
primer_name = ActiveCell.Value
CDNAPRIMER = CDNA_name & "_" & primer_name
For l = 1 To 3
Sheets("bilan").Select
Cells(row2, 1).Select
ActiveCell.Value = Source_pos_cDNA
Range("A1").Select
ActiveCell.Value = "Source_pos_cDNA"
Cells(row2, 2).Select
ActiveCell.Value = CDNA_name
Range("B1").Select
ActiveCell.Value = "CDNA_name"
Cells(row2, 3).Select
ActiveCell.Value = Source_cDNA_Well
Range("C1").Select
ActiveCell.Value = "Source_cDNA_Well"
Cells(row2, 4).Select
ActiveCell.Value = vol_CDNA
Range("D1").Select
ActiveCell.Value = "vol_cDNA"
Cells(row2, 5).Select
ActiveCell.Value = Dest_pos_cDNA
Range("E1").Select
ActiveCell.Value = "Dest_pos_cDNA"
Cells(row2, 6).Select
ActiveCell.Value = Dest_well_cDNA
Range("F1").Select
ActiveCell.Value = "Dest_well_cDNA"
Cells(row2, 7).Select
ActiveCell.Value = Source_BC_primer
Range("G1").Select
ActiveCell.Value = "Source_BC_primer"
Cells(row2, 8).Select
ActiveCell.Value = primer_name
Range("H1").Select
ActiveCell.Value = "primer_name"
Cells(row2, 9).Select
ActiveCell.Value = Source_BC_pos_primer
Range("I1").Select
ActiveCell.Value = "Source_BC_pos_primer"
Cells(row2, 10).Select
ActiveCell.Value = Vol_primer_mix
Range("J1").Select
ActiveCell.Value = "Vol_primer_mix"
Cells(row2, 11).Select
ActiveCell.Value = Dest_well_Primers
Range("K1").Select
ActiveCell.Value = "Dest_well_Primers"
Source_BC_pos_primer = Source_BC_pos_primer + 1
Sheets("SDS_file").Select
Range("A1").Select
ActiveCell.Value = "*** SDS Setup File Version"
Range("A2").Select
ActiveCell.Value = "*** Output Plate Size"
Range("A3").Select
ActiveCell.Value = "*** Output Plate ID"
Range("A4").Select
ActiveCell.Value = "*** Number of Detectors"
Range("B1").Select
ActiveCell.Value = "3"
Range("B2").Select
ActiveCell.Value = "384"
Range("B3") = Sheets("CDNA_Primer").Range("I5")
Range("B4").Select
ActiveCell.Value = primernumb
Range("A5").Select
ActiveCell.Value = "Detector"
Range("B5").Select
ActiveCell.Value = "Reporter"
Range("C5").Select
ActiveCell.Value = "Quencher"
Range("D5").Select
ActiveCell.Value = "Description"
Range("E5").Select
ActiveCell.Value = "Comments"
Sheets("CDNA_PRIMER").Range("B2:B" & Sheets("CDNA_PRIMER").Range("B65536").End(xlUp).Row).Copy
Range("A6").PasteSpecial xlPasteValues
Application.CutCopyMode = False
For Each c In Range("A6", [A65000].End(xlUp))
c.Offset(0, 1) = Sheets("CDNA_PRIMER").Range("I4")
Next
Sheets("layout").Select
Cells(Row, col).Range("A2").Select
ActiveCell.Value = CDNAPRIMER
Range("A1:X1").Select
Selection.Merge
Range("A1:X1").Select
ActiveCell.FormulaR1C1 = "cDNA+Primers"
Columns("A:X").EntireColumn.AutoFit
Sheets("CDNA_layout").Select
Cells(Row, col).Range("A2").Select
ActiveCell.Value = CDNA_name
Range("A1:X1").Select
Selection.Merge
Range("A1:X1").Select
ActiveCell.FormulaR1C1 = "cDNA Plate"
Sheets("Primer_layout").Select
Cells(Row, col).Range("A2").Select
ActiveCell.Value = primer_name
Range("A1:X1").Select
Selection.Merge
Range("A1:X1").Select
ActiveCell.FormulaR1C1 = "Primer Plate"
Sheets("CDNA_PRIMER").Select
Dest_well_cDNA = Dest_well_cDNA + 1
Dest_well_Primers = Dest_well_Primers + 1
col = col + 1
If col > 24 Then
col = 1
Row = Row + 1
End If
row2 = row2 + 1
row3 = Range("B4").Value
Next l
Next J
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
Next I
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
Application.Run "export"
End Sub
Sub export()
Sheets("bilan").Select
rep = ActiveWorkbook.Path & "\"
Sheets("Bilan").Copy
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs rep & "Biomek_file.csv", FileFormat:=xlCSVMSDOS
.Close 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
Workbooks.OpenText Filename:=rep & "Biomek_file.csv"
ActiveWorkbook.Save
ActiveWorkbook.Close True
End Sub