Snasui Excel Forum

Code: Select all

Sub update_vol2()

Dim ship As Long
Dim r_ship As Long
Dim p_fuelvol As Long
Dim p_select As Integer
Dim p_lube As Long
Dim i As Integer

i = 4

  'ตรงนี้ที่มีปันหาคือจะต้องสร้างฟังชันเพิ่มไปเครื่องๆตามจำนวนrowที่มีข้อมูล ถ้ามี ไม่เยอะก็พอไหวแต่ถ้ามีเป็น100ควรจะต้องทำอย่างไรครับ  
    ship = Sheets("Cluster Summary").Cells(26, 11).Value
    p_fuelvol = Sheets("Cluster Summary").Cells(26, 12).Value
    p_select = Sheets("Cluster Summary").Cells(26, 13).Value
    p_lube = Sheets("Cluster Summary").Cells(26, 14).Value
    
    r_ship = Sheets("Cal Sheet").Cells(i, 6).Value
    
    
Do
    If ship = r_ship Then
   
 
    Sheets("Cal Sheet").Cells(i, 12).Value = p_fuelvol * 12
    Sheets("Cal Sheet").Cells(i, 13).Value = p_select * 12
    Sheets("Cal Sheet").Cells(i, 14).Value = p_lube * 12
    
    End If
    i = i + 1
    r_ship = Sheets("Cal Sheet").Cells(i, 6).Value
    
Loop Until r_ship = 0


End Sub

Code: Select all

Dim rsAll As Range, rs As Range
Dim rtAll As Range, rt As Range
With Sheets("Cluster Summary")
    Set rsAll = .Range("k26", .Range("k" & .Rows.Count).End(xlUp))
End With
With Sheets("Cal Sheet")
    Set rtAll = .Range("f4", .Range("f" & .Rows.Count).End(xlUp))
End With
For Each rt In rtAll
    For Each rs In rsAll
        If CLng(rt.Value) = CLng(rs.Value) Then
            rt.Offset(0, 6).Value = rs.Offset(0, 1).Value * 12
            rt.Offset(0, 7).Value = rs.Offset(0, 2).Value * 12
            rt.Offset(0, 8).Value = rs.Offset(0, 3).Value * 12
        End If
    Next rs
Next rt