CODEWITHSUNDEEP
excelvba
RawrRawr7
RawrRawr7

Reputation: 353

Partial Text String VBA

I am trying to create a "If statement" where I check column B if it contains a partial string ("BMC-"). Then write "Bill of Material" in column E.

I already have tried using a formula, but I would like this to written as a macro.

=IF(ISNUMBER(SEARCH("BMC-",B14)), "Bill of Material", "")

  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

I am wanting the code to loop until the last row to find all the strings that contains the partial text "BMC-" in column B to write "Bill of Materials" in column E

Upvotes: 0

Views: 10631

Answers (3)

Miras
Miras

Reputation: 106

You can try this approach using split() function:

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

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26660

Just use the formula you already have, no looping required. Also declare your variables. Utilize variables in place of hard-coding constant values so that the code is easier to adjust and maintain. Something like this should work for you:

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

Upvotes: 1

Cyril
Cyril

Reputation: 6829

Tim has a solid Like case, though I tend to use 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

Upvotes: 1

Related Questions