部分文本字符串VBA

Question

我正在尝试创建一个"If语句“，如果它包含一个部分字符串("BMC-")，我将检查B列。然后在E栏中写上“材料清单”。

我已经试过使用公式了，但是我想把它写成宏。

=IF(ISNUMBER(搜索(“BMC-”，B14))，“材料清单”，"")

  Sub Descriptions()

  For r = 14 To Cells(Rows.Count, "B").End(xlUp).Row     ' From row 1 to 
  the last row with data

On Error Resume Next

If Cells(r, "B") = "BMC-9" > 0 Then
    Cells(r, "E").Value = "Bill of Materials"
End If

Next



End Sub

我希望代码循环到最后一行，以找到B列中包含部分文本"BMC-“的所有字符串，以便在E列中写入”物料清单“

Answer 1

尽管我倾向于使用Like ()，但提姆有一个坚实的InStr案例：

Sub Descriptions()
    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 Instr(Cells(r, "B").Value, "BMC-9") Then
            Cells(r, "E").Value = "Bill of Materials"
        End If
    Next
End Sub

Answer 2

只需使用您已经拥有的公式，不需要循环。同时声明变量。使用变量代替硬编码常量值，这样代码就更容易调整和维护。像这样的东西应该对你有用：

Sub tgr()

    'Declare variables
    Dim ws As Worksheet
    Dim lHeaderRow As Long
    Dim sSearchCol As String
    Dim sOutputCol As String
    Dim sTextToFind As String
    Dim sTextToWrite As String

    'Set this to the actual worksheet where you want the formula
    Set ws = ActiveWorkbook.ActiveSheet

    'Define variables
    lHeaderRow = 13                     'Header Row.  Actual data and results will start on the next row
    sSearchCol = "B"                    'Column to search for the text
    sOutputCol = "E"                    'Column to output results
    sTextToFind = "BMC-"                'Text to search for
    sTextToWrite = "Bill of Material"   'Text that will be output when search text is found

    'Use previously defined variables to establish range
    With ws.Range(sOutputCol & lHeaderRow + 1 & ":" & sOutputCol & ws.Cells(ws.Rows.Count, sSearchCol).End(xlUp).Row)
        If .Row <= lHeaderRow Then Exit Sub 'No data

        'Apply your formula to all rows in the range at once
        .Formula = "=IF(ISNUMBER(SEARCH(""" & sTextToFind & """," & sSearchCol & .Row & ")), """ & sTextToWrite & """, """")"

        'Convert cells to values
        .Value = .Value
    End With

End Sub

Answer 3

您可以使用split()函数尝试这种方法：

Sub NewCode()
    For r = 14 To Cells(Rows.Count, "B").End(xlUp).Row  
        Dim myArray As Variant
            myArray = Split(Cells(r, "B"), "BMC-")
            If UBound(myArray) > 0 Then
                Cells(r, "E").Value = "Bill of Material"
            End If     
    Next r
End Sub