vba - 宏 : copy paste cell if condition met

Question

有一个步骤卡住了，更新数据库中的库存编号(“D”列)

更新是基于项目的名称(nama barang)，所以如果form_penerimaan中的项目名称(列“C”)与数据库中的项目名称(列“B”)_gudang ，database_gudang中的库存将被更新。

但有一个问题，仅在第 2、9、10 行(黄色单元格)中更新。一行 3,4,5 也应该更新。

非常感谢您的帮助。

问候。

Sub Module1()
    s = 10
    OT1 = Sheets("Database_Gudang").Cells(Rows.Count, "D").End(xlUp).Row

    For j = 2 To OT1
        NB1 = Sheets("Database_Gudang").Cells(j, "B").Value

        Sheets("Form_Penerimaan").Activate
        If Cells(s, "C").Value = NB1 And Cells(s, "C").Value <> "" Then
            Sheets("Form_Penerimaan").Cells(s, "Q").Copy

            Sheets("Database_Gudang").Activate
            Sheets("Database_Gudang").Cells(j, "G").Select
            Selection.PasteSpecial Paste:=xlPasteValues

            s = s + 1
        End If
    Next j
End Sub

Answer 1

嗨，欢迎来到 stackoverflow :)

避免使用 .Select和 .Activate .直接使用变量和对象。您可能想查看 How to avoid using Select in Excel VBA

您正面临这个问题，因为您没有遍历第二张纸的单元格。

这是你正在尝试的吗？ ( 未测试 )

我已经对代码进行了注释，因此您在理解它时可能没有问题。如果你这样做了，那么分享确切的错误消息，我们将从那里获取它。

Sub Sample()
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim i As Long, j As Long
    Dim wsThisLRow As Long, wsThatLRow As Long

    '~~> Set your worksheets
    Set wsThis = ThisWorkbook.Sheets("Database_Gudang")
    Set wsThat = ThisWorkbook.Sheets("Form_Penerimaan")

    '~~> Find relevant last row in both sheets
    wsThisLRow = wsThis.Range("D" & wsThis.Rows.Count).End(xlUp).Row
    wsThatLRow = wsThat.Range("C" & wsThat.Rows.Count).End(xlUp).Row

    With wsThis
        '~~> Loop through cell in Database_Gudang
        For i = 2 To wsThisLRow
            '~~> Loop through cell in Form_Penerimaan
            For j = 10 To wsThatLRow
                '~~> Compare values and get values across if applicable
                If .Range("B" & i).Value = wsThat.Range("C" & j).Value Then
                    .Range("G" & i).Value = wsThat.Range("Q" & j).Value
                    Exit For
                End If
            Next j
        Next i
    End With
End Sub