excel - 检查整个列以查看文本是否存在。如果确实如此，则将其记录在不同的单元格中

Question

我正在尝试遍历一列以查看是否存在特定系列的文本。如果是这样，那么我想显示某个范围内存在哪些文本。

我已经尝试过 for 循环和每个单元格命令，但我不确定还要尝试什么命令。

在下面显示的整个列中，如果出现 AH、DF，我想在 Range B10 中写“AutoCAD Construction Issue Hard Copy”，在 B11 中写“Digital Files”。如果只出现“DF，P”，我想在范围 B10 中写入“数字文件”并在 B11 中打印。但是，如果它们三个都出现（如下图所示），我想在 B10 中写“AutoCAD Construction Issue Hard Copy”。B11 中的“数字文件”和 B12 中的打印。我的问题是，每当我创建此列表时，我希望列表从 B10 开始，并且列表之间没有间隙。列表的顺序必须是 AutoCAD Construction Issue Hard Copy、Digital Files 和 Prints。

我的代码粘贴在下面：

Sub Descriptions()
    Range("A14:A305").ClearContents

    For r = 14 To Cells(Rows.Count, "B").End(xlUp).Row
        On Error Resume Next  'get rid of that... find error and fix/build logic, don't ignore it

        If Range("A1").Value = "30% Design Review" Or Range("A1").Value = "Final Design Review" Then
            If InStr(Cells(r, "B").Value, "BMC-9") Then
                Cells(r, "E").Value = "Bill of Materials"
                Cells(r, "A").Value = "DF, P"
            ElseIf InStr(Cells(r, "B").Value, "MC-9") Or InStr(Cells(r, "B").Value, "CSR-9") Or InStr(Cells(r, "B").Value, "LC-9") Then
                Cells(r, "A").Value = "DF, P"
            End If
        ElseIf Range("A1").Value = "Construction Submittal" Then
            If InStr(Cells(r, "B").Value, "BMC-9") Then
                Cells(r, "E").Value = "Bill of Materials"
                Cells(r, "A").Value = "DF, P"
            ElseIf InStr(Cells(r, "B").Value, "MC-9") Or InStr(Cells(r, "B").Value, "CSR-9") Or InStr(Cells(r, "B").Value, "LC-9") Then
                Cells(r, "A").Value = "AH, DF"
            End If
        End If
    Next

    For r = 14 To Cells(Rows.Count, "B").End(xlUp).Row
        If Cells(r, "A").Value = "DF, P" Then
            Range("B10").Value = "Digital Files"
            Range("B11").Value = "Prints"
        ElseIf Cells(r, "A").Value = "AH, DF" Then
            Range("B10").Value = "AutoCAD Construction Issue Hard Copy"
            Range("B11").Value = "Digital Files"
        End If
    Next
End Sub

新编辑 04/11/2019

标签： excelvba

Answer 1

Sub SeachDesc()
Dim SrchRng As Range, cel As Range
Set SrchRng = Range("A14:A305")
Range("B10:B12").ClearContents
For Each cel In SrchRng
    If cel.Value = "DF, P" Then
        Range("B10").Value = "Digital Files"
        Range("B11").Value = "Print(s)"
    ElseIf cel.Value = "AH, DF" Then
        Range("B10").Value = "AutoCAD Construction Issue Hard Copy"
        Range("B11").Value = "Digital Files"
    End If
Next cel
    If Range("B11").Value = "Print(s)" And Range("B12").Value = "Print(s)" Then
        Range("B10").Value = "AutoCAD Construction Issue Hard Copy"
        Range("B11").Value = "Digital Files"
        Range("B12").Value = "Print(s)"
    End If
End Sub