excel - 检查一个单元格的多个数据值

Question

有没有更有效的方法来编码:

Do
TempRow = TempRow + 1
   If (Worksheets("Source").Cells(TempRow, TempColumn) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 1) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 2) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 3) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 4) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 5) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 6) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 7) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 8) = "808" Or Worksheets("Source").Cells(TempRow, TempColumn + 9) = "808") Then
     If (Worksheets("Source").Cells(TempRow, TempColumn) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "147" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "147") Then
       If (Worksheets("Source").Cells(TempRow, TempColumn) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "288" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "288") Then
         If (Worksheets("Source").Cells(TempRow, TempColumn) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "468" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "468") Then
           If (Worksheets("Source").Cells(TempRow, TempColumn) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "661" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "661") Then
             If (Worksheets("Source").Cells(TempRow, TempColumn) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "662" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "662") Then
               If (Worksheets("Source").Cells(TempRow, TempColumn) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "663" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "663") Then
                 If (Worksheets("Source").Cells(TempRow, TempColumn) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 1) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 2) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 3) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 4) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 5) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 6) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 7) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 8) <> "664" And Worksheets("Source").Cells(TempRow, TempColumn + 9) <> "664") Then
                        Worksheets("Source").Cells(TempRow, 260) = "1"
                 Else: Worksheets("Source").Cells(TempRow, 260) = "0"
                 End If
               End If
             End If
           End If
         End If
       End If
    End If
  End If
Loop While Not IsEmpty(Worksheets("Source").Cells(TempRow, TempColumn))

Answer 1

好的，粘贴到我的 VBE 后，我意识到它并没有看起来那么糟糕。

这是使它更高效(并且更易于阅读/编辑)的一个巨大开始:

With Worksheets("Source")

    Do
    tempRow = tempRow + 1

        If Not .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="808", lookat:=xWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="147", lookat:=xlWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="288", lookat:=xlWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="468", lookat:=xlWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="661", lookat:=xlWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="662", lookat:=xlWhole) Is Nothing _
            And .Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="663", lookat:=xlWhole) Is Nothing _
            And Cells(tempRow, TempColumn).Resize(1, 10).Find(What:="664", lookat:=xlWhole) Is Nothing Then
                Worksheets("Source").Cells(tempRow, 260) = "1"
        Else: Worksheets("Source").Cells(tempRow, 260) = "0"

        End If

    Loop While Not IsEmpty(.Cells(tempRow, TempColumn))

End With