Need to say 'contains' instead of equals

[katie215]

Hi-

I have some code that currently finds cells with specific text and then merges them with the 3 blank cells to the right (in order to make the text 1 line as it wraps around in the first column). I want to change this so that instead of saying if the cell equals the text, to instead say if it contains the text to merge the cells (as there will be minor tweaks to the text every few months and if I can use contain instead I won't have to constantly update the code as I know the beginning will stay the same).

So for example on the first one (of the 3 I'm searching for) I'd like to say if the cell contains "Other grades include: " as opposed to saying exactly what the cell equals.

Any help is greatly appreciated!

Here's the code:
Sub merge_cells(wb)


Dim ws As Worksheet
Dim C As Range
For Each ws In Sheets
For Each C In ws.Range("a2:a" & ws.Range("A" & Rows.Count).End(xlUp).Row)

On Error GoTo 10

If C.Value = "Other grades include: AU, AUX DX, FEX, I, IP, P, PS, S, SX, U, UX" Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If

If C.Value = "Data reflect grades posted as of 'January 17, 2014'" Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If


If C.Value = "Report produced by the Office of Institutional Research." Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If




Next
10
Next

End Sub

[playaller]

You would simply change this line to change the = to LIKE and to add * Wildcards like so


Change original:

If C.Value = "Other grades include: AU, AUX DX, FEX, I, IP, P, PS, S, SX, U, UX" Then

to updated:

If C.Value Like "*Other grades include:*" Then

Line above says if any contains the words "Other grades include:" then proceed to merge
You can remove the wildcard from the front if you know the cells searching will always start with the phrase. Then wild card will pick up at the end.



You can also do this for the other if statement lines.


Full code modified:

Sub merge_cells(wb)


Dim ws As Worksheet
Dim C As Range
For Each ws In Sheets
For Each C In ws.Range("a2:a" & ws.Range("A" & Rows.Count).End(xlUp).Row)

On Error GoTo 10

If C.Value Like "*Other grades include:*" Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If

If C.Value Like "*Data reflect grades posted as of*" Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If


If C.Value Like "*Report produced by the Office of*" Then
C.Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Merge
ActiveCell.Select
End If


Next
10
Next

End Sub

[katie215]

That did it! Thank you so much!!

[playaller]

You're more than welcome. Glad I could help and thank you for the reputation!