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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
|
Public Sub EffortMB1cycle1()
'
'-----changer la valeur de la contrainte MB1 en fonction de la pression du Step-----
'
pdesign = Sheets("Input").Cells(9, 5).Value 'sauvegarde de la P design en statique
ligne = 4
For s = 1 To 24 Step 1
ligne = ligne + 1
pdf = Sheets("Cycle").Cells(ligne, 3).Value
Sheets("Input").Cells(9, 5).FormulaR1C1 = pdf
'-----saisi de la valeur dans la colonne MB1-----
Sheets("Cycle").Cells(ligne, 4).FormulaR1C1 = Sheets("Efforts MB1").Cells(6, 10).Value
'-----saisi de la valeur de Seq des soudures si pdf est positif, sinon mettre 0
If pdf > 0 Then
Sheets("Cycle").Cells(ligne, 9).FormulaR1C1 = Sheets("Console MB1").Cells(26, 40).Value
Sheets("Cycle").Cells(ligne, 10).FormulaR1C1 = Sheets("Console MB1").Cells(61, 40).Value
Sheets("Cycle").Cells(ligne, 11).FormulaR1C1 = Sheets("Console SB1").Cells(26, 40).Value
Sheets("Cycle").Cells(ligne, 12).FormulaR1C1 = Sheets("Console SB1").Cells(61, 40).Value
Sheets("Cycle").Cells(ligne, 13).FormulaR1C1 = Sheets("Console SB2").Cells(26, 40).Value
Sheets("Cycle").Cells(ligne, 14).FormulaR1C1 = Sheets("Console SB2").Cells(61, 40).Value
Sheets("Cycle").Cells(ligne, 15).FormulaR1C1 = Sheets("Console SB3").Cells(26, 40).Value
Sheets("Cycle").Cells(ligne, 16).FormulaR1C1 = Sheets("Console SB3").Cells(61, 40).Value
Sheets("Cycle").Cells(ligne, 17).FormulaR1C1 = Sheets("Console SB4").Cells(26, 40).Value
Sheets("Cycle").Cells(ligne, 18).FormulaR1C1 = Sheets("Console SB4").Cells(61, 40).Value
Else
For colonne = 9 To 18 Step 1
Sheets("Cycle").Cells(ligne, colonne).FormulaR1C1 = 0
Next colonne
End If
Next s
'-----retour à la valeur de P design en statique-----
Sheets("Input").Cells(9, 5).FormulaR1C1 = pdesign
'-----mettre la valeur du step correspondant à la P max en S5-----
Dim Plage As Range, c As Range
With Worksheets("Cycle")
Set Plage = .Range("C5:C28")
' mf = Application.WorksheetFunction.Min(Plage)
mf = Application.WorksheetFunction.Max(Plage)
Set c = Plage.Find(mf, LookIn:=xlFormulas, lookat:=xlWhole)
' If Not c Is Nothing Then MsgBox c.Offset(0, 0).Address
' If Not c Is Nothing Then MsgBox c.Offset(0, -2).Value
Sheets("Cycle").Cells(5, 19).FormulaR1C1 = c.Offset(0, -2).Value
End With
Set c = Nothing
Set Plage = Nothing
Call concatenercycle1
End Sub |
Partager