Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim iRow As Integer
Dim jRow As Integer
iRow = 4
jRow = 5
Do While Worksheets("Program").Range("C" & jRow).Value <> ""
If Worksheets("ProductDB").Range("D" & iRow).Value = Worksheets("Program").Range("C" & jRow).Value Then
Worksheets("Program").Range("D" & jRow).Value = Worksheets("ProductDB").Range("E" & iRow).Value
Worksheets("Program").Range("E" & jRow).Value = Worksheets("ProductDB").Range("F" & iRow).Value
End If
iRow = iRow + 1
jRow = jRow + 1
Loop
End Sub

Code: Select all

Private Sub CommandButton1_Click()
'    Dim iRow As Integer
'    Dim jRow As Integer
'    iRow = 4
'    jRow = 5
'    Do While Worksheets("ProductDB").Range("B" & iRow).Value <> ""
'        If Worksheets("ProductDB").Range("C" & iRow).Value = Worksheets("Program").Range("C & jRow").Value Then
'            Worksheets("Program").Range("D" & jRow).Value = Worksheets("ProductDB").Range("D" & iRow).Value
'            Worksheets("Program").Range("E" & jRow).Value = Worksheets("ProductDB").Range("E" & iRow).Value
'            Exit Sub
'        End If
'        iRow = iRow + 1
'    Loop
    Dim rSource As Range
    Dim rs As Range
    Dim rTarget As Range
    Dim rt As Range
    Dim lng As Long
    With Sheets("ProductDB")
        Set rSource = .Range("C4", .Range("C" & Rows.Count) _
            .End(xlUp))
    End With
    With Sheets("Program")
        Set rTarget = .Range("C5", .Range("C" & Rows.Count) _
            .End(xlUp))
    End With
    For Each rt In rTarget
        For Each rs In rSource
            If rs = rt Then
                rt.Offset(0, 1) = rs.Offset(0, 1)
                rt.Offset(0, 2) = rs.Offset(0, 2)
                lng = lng + 1
            End If
        Next rs
    Next rt
    If lng = 0 Then
        MsgBox "ไม่พบรหัสสินค้าที่กำหนด", vbOKOnly
    End If
End Sub