Snasui Excel Forum

ต้องการเรีบงลำดับงานตามค่าของเวลาในแนวแถว โดยจัดให้แต่ละแถว มีเวลารวมกันไม่เกิน 9 นาที พร้อมดีเขียนโคัดตามความเข้าใจมาครับ พึ่งหัดเล่นครับแนะนำด้วยครับ
ผมแนวไฟล์ที่เขียนมาด้วยครับ แต่เวลาที่เปลี่ยนค่า มีเวลารวมกัน ต้องเช็คใหม่ตลอดครับ เขียนด้วยโค้ดง่ายๆครับ


Sub Macro3()
'
' จัดสถานี โครโมโซม1
'
Dim i, j, xg1, xg2, xg3, xg4, xg5, xg6, xg7, xg8, xg9, xg10, xg11

xg1 = Sheet5.Cells(32, 2)
xg2 = Sheet5.Cells(32, 3)
xg3 = Sheet5.Cells(32, 4)
xg4 = Sheet5.Cells(32, 5)
xg5 = Sheet5.Cells(32, 6)
xg6 = Sheet5.Cells(32, 7)
xg7 = Sheet5.Cells(32, 8)
xg8 = Sheet5.Cells(32, 9)
xg9 = Sheet5.Cells(32, 10)
xg10 = Sheet5.Cells(32, 11)
xg11 = Sheet5.Cells(32, 12)

For i = 1 To 2
For j = 1 To 11
'.....................................................................................
If Sheet5.Cells(35, 2) > 0 Then
Sheet5.Cells(i + 34, 2) = Sheet5.Cells(i + 30, 2)
End If
'......................................................
If Sheet5.Cells(36, 2) + xg2 > 7 Then
Sheet5.Cells(i + 38, 3) = Sheet5.Cells(i + 30, 3)
Else
Sheet5.Cells(i + 34, 3) = Sheet5.Cells(i + 30, 3)
End If
'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + xg3 > 9 Then
Sheet5.Cells(i + 38, 4) = Sheet5.Cells(i + 30, 4)
Sheet5.Cells(i + 34, 4) = 0
Else
Sheet5.Cells(i + 34, 4) = Sheet5.Cells(i + 30, 4)
Sheet5.Cells(i + 38, 4) = 0
End If

'..............................................
If Sheet5.Cells(40, 3) + xg3 > 9 Then
Sheet5.Cells(i + 42, 4) = Sheet5.Cells(i + 30, 4)
Sheet5.Cells(i + 38, 4) = 0
Else
Sheet5.Cells(i + 38, 4) = Sheet5.Cells(i + 30, 4)
Sheet5.Cells(i + 42, 4) = 0
End If
'...........................................

'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + xg4 > 9 Then
Sheet5.Cells(i + 38, 5) = Sheet5.Cells(i + 30, 5)
Sheet5.Cells(i + 42, 5) = 0
Sheet5.Cells(i + 34, 5) = 0
Else
Sheet5.Cells(i + 34, 5) = Sheet5.Cells(i + 30, 5)
Sheet5.Cells(i + 38, 5) = 0
Sheet5.Cells(i + 42, 5) = 0
End If

'..............................................
' If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + xg4 > 9Then
' Sheet5.Cells(i + 42, 5) = Sheet5.Cells(i + 30, 5)
'Sheet5.Cells(i + 38, 5) = 0
' Sheet5.Cells(i + 34, 5) = 0
'Else
' Sheet5.Cells(i + 38, 5) = Sheet5.Cells(i + 30, 5)
' Sheet5.Cells(i + 34, 5) = 0
' Sheet5.Cells(i + 42, 5) = 0
' End If


'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + xg5 > 9 Then
Sheet5.Cells(i + 38, 6) = Sheet5.Cells(i + 30, 6)

Else
Sheet5.Cells(i + 34, 6) = Sheet5.Cells(i + 30, 6)

End If

'..............................................
' If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + xg5 > 9 Then
' Sheet5.Cells(i + 42, 6) = Sheet5.Cells(i + 30, 6)
' Sheet5.Cells(i + 38, 6) = 0
' Sheet5.Cells(i + 34, 6) = 0
' Else
' Sheet5.Cells(i + 38, 6) = Sheet5.Cells(i + 30, 6)
' Sheet5.Cells(i + 42, 6) = 0
' End If

'..........................................
'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + xg6 > 9 Then
Sheet5.Cells(i + 38, 7) = Sheet5.Cells(i + 30, 7)

Else
Sheet5.Cells(i + 34, 7) = Sheet5.Cells(i + 30, 7)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + xg6 > 9 Then
Sheet5.Cells(i + 42, 7) = Sheet5.Cells(i + 30, 7)
Sheet5.Cells(i + 38, 7) = 0
Sheet5.Cells(i + 34, 7) = 0
Else
Sheet5.Cells(i + 38, 7) = Sheet5.Cells(i + 30, 7)
Sheet5.Cells(i + 42, 7) = 0
End If
'.............................................................
If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + xg6 > 9 Then
Sheet5.Cells(i + 46, 7) = Sheet5.Cells(i + 30, 7)
Sheet5.Cells(i + 38, 7) = 0
Sheet5.Cells(i + 34, 7) = 0
Sheet5.Cells(i + 42, 7) = 0
Else
Sheet5.Cells(i + 42, 7) = Sheet5.Cells(i + 30, 7)
Sheet5.Cells(i + 46, 7) = 0
End If
'................................................................
'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + Sheet5.Cells(36, 7) + xg7 > 9 Then
Sheet5.Cells(i + 38, 8) = Sheet5.Cells(i + 30, 8)

Else
Sheet5.Cells(i + 34, 8) = Sheet5.Cells(i + 30, 8)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + Sheet5.Cells(40, 7) + xg7 > 9 Then
Sheet5.Cells(i + 42, 8) = Sheet5.Cells(i + 30, 8)
Sheet5.Cells(i + 38, 8) = 0
Sheet5.Cells(i + 34, 8) = 0
Else
Sheet5.Cells(i + 38, 8) = Sheet5.Cells(i + 30, 8)
Sheet5.Cells(i + 42, 8) = 0
End If
'.............................................................
' If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + Sheet5.Cells(44, 7) + xg7 > 9 Then
' Sheet5.Cells(i + 46, 8) = Sheet5.Cells(i + 30, 8)
' Sheet5.Cells(i + 38, 8) = 0
' Sheet5.Cells(i + 34, 8) = 0
' Sheet5.Cells(i + 42, 8) = 0
' Else
' Sheet5.Cells(i + 42, 8) = Sheet5.Cells(i + 30, 8)
' Sheet5.Cells(i + 46, 8) = 0
'End If

'......................................................................................
'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + Sheet5.Cells(36, 7) + Sheet5.Cells(36, 8) + xg8 > 9 Then
Sheet5.Cells(i + 38, 9) = Sheet5.Cells(i + 30, 9)

Else
Sheet5.Cells(i + 34, 9) = Sheet5.Cells(i + 30, 9)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + Sheet5.Cells(40, 7) + Sheet5.Cells(40, 8) + xg8 > 9 Then
Sheet5.Cells(i + 42, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 38, 9) = 0
Sheet5.Cells(i + 34, 9) = 0
Else
Sheet5.Cells(i + 38, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 42, 9) = 0
End If
'.............................................................
If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + Sheet5.Cells(44, 7) + Sheet5.Cells(44, 8) + xg8 > 9 Then
Sheet5.Cells(i + 46, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 38, 9) = 0
Sheet5.Cells(i + 34, 9) = 0
Sheet5.Cells(i + 42, 9) = 0
Else
Sheet5.Cells(i + 42, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 46, 9) = 0
End If
'......................................
If Sheet5.Cells(48, 3) + Sheet5.Cells(48, 4) + Sheet5.Cells(48, 5) + Sheet5.Cells(48, 6) + Sheet5.Cells(48, 7) + Sheet5.Cells(48, 8) + xg8 > 9 Then
Sheet5.Cells(i + 50, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 38, 9) = 0
Sheet5.Cells(i + 46, 9) = 0
Sheet5.Cells(i + 42, 9) = 0
Else
Sheet5.Cells(i + 46, 9) = Sheet5.Cells(i + 30, 9)
Sheet5.Cells(i + 50, 9) = 0
End If

'...........................................................................


'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + Sheet5.Cells(36, 7) + Sheet5.Cells(36, 8) + Sheet5.Cells(36, 9) + xg9 > 9 Then
Sheet5.Cells(i + 38, 10) = Sheet5.Cells(i + 30, 10)

Else
Sheet5.Cells(i + 34, 10) = Sheet5.Cells(i + 30, 10)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + Sheet5.Cells(40, 7) + Sheet5.Cells(40, 8) + Sheet5.Cells(40, 9) + xg9 > 9 Then
Sheet5.Cells(i + 42, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 38, 10) = 0
Sheet5.Cells(i + 34, 10) = 0
Else
Sheet5.Cells(i + 38, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 42, 10) = 0
End If
'.............................................................
If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + Sheet5.Cells(44, 7) + Sheet5.Cells(44, 8) + Sheet5.Cells(44, 9) + xg9 > 9 Then
Sheet5.Cells(i + 46, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 38, 10) = 0
Sheet5.Cells(i + 34, 10) = 0
Sheet5.Cells(i + 42, 10) = 0
Else
Sheet5.Cells(i + 42, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 46, 10) = 0
End If
'......................................
If Sheet5.Cells(48, 3) + Sheet5.Cells(48, 4) + Sheet5.Cells(48, 5) + Sheet5.Cells(48, 6) + Sheet5.Cells(48, 7) + Sheet5.Cells(48, 8) + Sheet5.Cells(48, 9) + xg9 > 9 Then
Sheet5.Cells(i + 50, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 38, 10) = 0
Sheet5.Cells(i + 46, 10) = 0
Sheet5.Cells(i + 42, 10) = 0
Else
Sheet5.Cells(i + 46, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 50, 10) = 0
End If
'......................................
If Sheet5.Cells(52, 3) + Sheet5.Cells(52, 4) + Sheet5.Cells(52, 5) + Sheet5.Cells(52, 6) + Sheet5.Cells(52, 7) + Sheet5.Cells(52, 8) + Sheet5.Cells(52, 9) + xg9 > 9 Then
Sheet5.Cells(i + 54, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 50, 10) = 0
Sheet5.Cells(i + 46, 10) = 0
Sheet5.Cells(i + 42, 10) = 0
Else
Sheet5.Cells(i + 50, 10) = Sheet5.Cells(i + 30, 10)
Sheet5.Cells(i + 54, 10) = 0
End If

'...................................................................................................


'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + Sheet5.Cells(36, 7) + Sheet5.Cells(36, 8) + Sheet5.Cells(36, 9) + Sheet5.Cells(36, 10) + xg10 > 9 Then
Sheet5.Cells(i + 38, 11) = Sheet5.Cells(i + 30, 11)

Else
Sheet5.Cells(i + 34, 11) = Sheet5.Cells(i + 30, 11)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + Sheet5.Cells(40, 7) + Sheet5.Cells(40, 8) + Sheet5.Cells(40, 9) + Sheet5.Cells(40, 10) + xg10 > 9 Then
Sheet5.Cells(i + 42, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 38, 11) = 0
Sheet5.Cells(i + 34, 11) = 0
Else
Sheet5.Cells(i + 38, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 42, 11) = 0
End If
'.............................................................
If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + Sheet5.Cells(44, 7) + Sheet5.Cells(44, 8) + Sheet5.Cells(44, 9) + Sheet5.Cells(44, 10) + xg10 > 9 Then
Sheet5.Cells(i + 46, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 38, 11) = 0
Sheet5.Cells(i + 34, 11) = 0
Sheet5.Cells(i + 42, 11) = 0
Else
Sheet5.Cells(i + 42, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 46, 11) = 0
End If
'......................................
If Sheet5.Cells(48, 3) + Sheet5.Cells(48, 4) + Sheet5.Cells(48, 5) + Sheet5.Cells(48, 6) + Sheet5.Cells(48, 7) + Sheet5.Cells(48, 8) + Sheet5.Cells(48, 9) + Sheet5.Cells(48, 10) + xg10 > 9 Then
Sheet5.Cells(i + 50, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 38, 11) = 0
Sheet5.Cells(i + 46, 11) = 0
Sheet5.Cells(i + 42, 11) = 0
Else
Sheet5.Cells(i + 46, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 50, 11) = 0
End If
'......................................
If Sheet5.Cells(52, 3) + Sheet5.Cells(52, 4) + Sheet5.Cells(52, 5) + Sheet5.Cells(52, 6) + Sheet5.Cells(52, 7) + Sheet5.Cells(52, 8) + Sheet5.Cells(52, 9) + Sheet5.Cells(52, 10) + xg10 > 9 Then
Sheet5.Cells(i + 54, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 50, 11) = 0
Sheet5.Cells(i + 46, 11) = 0
Sheet5.Cells(i + 42, 11) = 0
Else
Sheet5.Cells(i + 50, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 54, 11) = 0
End If



'......................................
If Sheet5.Cells(56, 3) + Sheet5.Cells(56, 4) + Sheet5.Cells(56, 5) + Sheet5.Cells(56, 6) + Sheet5.Cells(56, 7) + Sheet5.Cells(56, 8) + Sheet5.Cells(56, 9) + Sheet5.Cells(56, 10) + xg10 > 9 Then
Sheet5.Cells(i + 58, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 50, 11) = 0
Sheet5.Cells(i + 46, 11) = 0
Sheet5.Cells(i + 54, 11) = 0
Else
Sheet5.Cells(i + 54, 11) = Sheet5.Cells(i + 30, 11)
Sheet5.Cells(i + 58, 11) = 0
End If


'...........................................


'..........................................................
If Sheet5.Cells(36, 2) + Sheet5.Cells(36, 3) + Sheet5.Cells(36, 4) + Sheet5.Cells(36, 5) + Sheet5.Cells(36, 6) + Sheet5.Cells(36, 7) + Sheet5.Cells(36, 8) + Sheet5.Cells(36, 9) + Sheet5.Cells(36, 10) + Sheet5.Cells(36, 11) + xg11 > 9 Then
Sheet5.Cells(i + 38, 12) = Sheet5.Cells(i + 30, 12)

Else
Sheet5.Cells(i + 34, 12) = Sheet5.Cells(i + 30, 12)

End If

'..............................................
If Sheet5.Cells(40, 3) + Sheet5.Cells(40, 4) + Sheet5.Cells(40, 5) + Sheet5.Cells(40, 6) + Sheet5.Cells(40, 7) + Sheet5.Cells(40, 8) + Sheet5.Cells(40, 9) + Sheet5.Cells(40, 10) + Sheet5.Cells(40, 11) + xg11 > 9 Then
Sheet5.Cells(i + 42, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 38, 12) = 0
Sheet5.Cells(i + 34, 12) = 0
Else
Sheet5.Cells(i + 38, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 42, 12) = 0
End If
'.............................................................
If Sheet5.Cells(44, 3) + Sheet5.Cells(44, 4) + Sheet5.Cells(44, 5) + Sheet5.Cells(44, 6) + Sheet5.Cells(44, 7) + Sheet5.Cells(44, 8) + Sheet5.Cells(44, 9) + Sheet5.Cells(44, 10) + Sheet5.Cells(44, 11) + xg11 > 9 Then
Sheet5.Cells(i + 46, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 38, 12) = 0
Sheet5.Cells(i + 34, 12) = 0
Sheet5.Cells(i + 42, 12) = 0
Else
Sheet5.Cells(i + 42, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 46, 12) = 0
End If
'......................................
If Sheet5.Cells(48, 3) + Sheet5.Cells(48, 4) + Sheet5.Cells(48, 5) + Sheet5.Cells(48, 6) + Sheet5.Cells(48, 7) + Sheet5.Cells(48, 8) + Sheet5.Cells(48, 9) + Sheet5.Cells(48, 10) + Sheet5.Cells(48, 11) + xg11 > 9 Then
Sheet5.Cells(i + 50, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 38, 12) = 0
Sheet5.Cells(i + 46, 12) = 0
Sheet5.Cells(i + 42, 12) = 0
Else
Sheet5.Cells(i + 46, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 50, 12) = 0
End If
'......................................
If Sheet5.Cells(52, 3) + Sheet5.Cells(52, 4) + Sheet5.Cells(52, 5) + Sheet5.Cells(52, 6) + Sheet5.Cells(52, 7) + Sheet5.Cells(52, 8) + Sheet5.Cells(52, 9) + Sheet5.Cells(52, 10) + Sheet5.Cells(52, 11) + xg11 > 9 Then
Sheet5.Cells(i + 54, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 50, 12) = 0
Sheet5.Cells(i + 46, 12) = 0
Sheet5.Cells(i + 42, 12) = 0
Else
Sheet5.Cells(i + 50, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 54, 12) = 0
End If



'......................................
If Sheet5.Cells(56, 3) + Sheet5.Cells(56, 4) + Sheet5.Cells(56, 5) + Sheet5.Cells(56, 6) + Sheet5.Cells(56, 7) + Sheet5.Cells(56, 8) + Sheet5.Cells(56, 9) + Sheet5.Cells(56, 10) + Sheet5.Cells(56, 11) + xg11 > 9 Then
Sheet5.Cells(i + 58, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 50, 12) = 0
Sheet5.Cells(i + 46, 12) = 0
Sheet5.Cells(i + 54, 12) = 0
Else
Sheet5.Cells(i + 54, 12) = Sheet5.Cells(i + 30, 12)
Sheet5.Cells(i + 58, 12) = 0
End If

'......................................
' If Sheet5.Cells(60, 3) + Sheet5.Cells(60, 4) + Sheet5.Cells(60, 5) + Sheet5.Cells(60, 6) + Sheet5.Cells(60, 7) + Sheet5.Cells(60, 8) + Sheet5.Cells(60, 9) + Sheet5.Cells(60, 10) + Sheet5.Cells(60, 11) + xg11 > 9 Then
' Sheet5.Cells(i + 62, 12) = Sheet5.Cells(i + 30, 12)
' Sheet5.Cells(i + 50, 12) = 0
' Sheet5.Cells(i + 58, 12) = 0
' Sheet5.Cells(i + 54, 12) = 0
' Else
' Sheet5.Cells(i + 58, 12) = Sheet5.Cells(i + 30, 12)
' Sheet5.Cells(i + 62, 12) = 0
' End If

Next j
Next i


End Sub

ช่วยโพสต์ Code ให้เป็น Code โดยดูจากกระทู้นี้ครับ viewtopic.php?f=3&t=1187 นอกจากนี้ช่วยแจ้งเพิ่มเติมว่า Code ที่เขียนมานั้นอยู่ใน Module ใด ปัญหาคืออะไร แสดงตัวอย่างคำตอบที่ต้องการไว้ด้วยก็จะดีเพื่อจะได้เข้าใจตรงกันครับ