IF和For循环中的VBA For循环

Question

我正在尝试编写一个宏来将管家的名字放在一个单元格中，如果他们被分配到那个类别的话。到目前为止，我写了这段代码，但它不起作用。我正在尝试获取它，以便如果一个工作表中列的单元格与另一个工作表中的单元格匹配，并且如果匹配，则它将在单独的单元格中打印管家的姓名，以确定该类别归该人员所有。

这些数字位于工作表Demetri中的E27到E38范围内，我想查看工作表Share_Dump中BE4到BE163803范围内的单元格是否在Demetri工作表的范围内。

Sub steward_products()
Dim d, s As Worksheet

Set d = Worksheets("Demetri")
Set s = Worksheets("Share_Dump")

For i = 4 To 163803 Step 1
    For j = 27 To 38 Step 1
        If s.Cells(i, 3) = d.Cells(j, 5) Then
            s.Cells(i, 57) = "Demetri"
        End If
    Next j
Next i

Answer 1

请尝试此代码。我想它会满足你的需要。

Option Explicit

Sub Steward_Products()
    ' 236

    ' use descriptive names and use the declarations to explain them
    ' use Option Explicit and capitalization to avoid typos
    Dim WsSteward   As Worksheet                ' Demetri
    Dim WsDump      As Worksheet                ' Share_Dump
    Dim Fnd         As Range

    Set WsSteward = Worksheets("Demetri")
    Set WsDump = Worksheets("Share_Dump")

    With WsDump
        ' presuming that columns 28:38 are not longer than column 27
        Set Fnd = .Range(.Cells(4, 27), .Cells(.Rows.Count, 27).End(xlUp)) _
                  .Resize(, 12)
        
        Set Fnd = Fnd.Find(WsSteward.Cells(5, "J").Value, _
                           LookIn:=xlValues, LookAt:=xlWhole)
    
        If Fnd Is Nothing Then
            MsgBox "Product """ & WsSteward.Cells(5, 10).Value & """ wasn't found.", _
                   vbInformation, "Invalid product description"
        Else
            .Cells(Fnd.Row, 57) = "Demetri"
        End If
    End With
End Sub