Partial Text String VBA

Question

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

get rid of the On Error Resume Next as well. If there is an error, you will want to define an error handler. — Zack E
– Zack E, Commented Apr 4, 2019 at 16:36
(You could always turn on the macro recorder, enter your formula, then see the code generated. It'll show you how you can use that exact formula in VBA.) — BruceWayne
– BruceWayne, Commented Apr 4, 2019 at 16:59

Cyril · Accepted Answer · 2019-04-04 16:56:06Z

1

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

answered Apr 4, 2019 at 16:56

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cyril Over a year ago

@ChristopherLee please mark as answered if your question is solved

tigeravatar · Accepted Answer · 2019-04-04 17:10:00Z

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

Miras · Accepted Answer · 2019-04-04 17:20:53Z

0

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

answered Apr 4, 2019 at 17:20

Miras

1065 bronze badges

Collectives™ on Stack Overflow

Partial Text String VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related