Use if then statement to check if text in a cell contains a string variable...

[chazrab]

This code works perfectly every time. But the value of x must match EXACTLY the contents of the cell value

Sub SEARCHANDCOPY()
Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim x As String
Dim finalrow As Integer
Dim i As Integer
Set datasheet = Worksheets("Sheet1")
Set reportsheet = Worksheets("Sheet2")
x = reportsheet.Range("H1").value
reportsheet.Range("A5:G200").ClearContents
datasheet.Select
finalrow = Cells(Rows.count, 1).End(xlUp).Row
 For i = 2 To finalrow
      If Cells(i, 4) = x Then
         Range(Cells(i, 1), Cells(i, 9)).copy
         reportsheet.Select
         Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
         datasheet.Select
      End If
 Next i
reportsheet.Select
Range("H1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

This code will find all cells which contain ONLY "pymt" and copy them to reportsheet.
Great.
BUT what if the value of x is CONTAINED WITHIN a cell with other text before and after x ?
Is there a way to use wildcard or pattern searches with x suchas **x** etc. so that the code will pick up all values of x whether
at the beginning, in the middle or at the end of any text in a cell ? I prefer to use an if then statement
if possible.
Please help on this. Will be very much appreciated.
THanks,
cr

 

[Fluff]

How about

If InStr(1, Cells(i, 4), x, vbTextCompare) > 0 Then

 

[Peter_SSs]

No data to test with but does this fix it?

If Cells(i, 4) = x Then
If Cells(i, 4).Value Like "*" & x & "*" Then

 

[chazrab]

How about

If InStr(1, Cells(i, 4), x, vbTextCompare) > 0 Then

This very simple line of code works absolutely great. The value of the variable can be found anywhere WITHIN a cell containing multiple lines
of text. This is very similar to using the FIND LookinxlPartial) except it uses an if statement without having to bother with using a FIndnext
loop or a Do Until loop. Very helpful nd very simple. Thanks again Fluff

 

[chazrab]

If Cells(i, 4).Value Like "*" & x & "*" Then

Hi Peter - yes this also works perfectly. Obviously there's more than one way to do things in VBA. And it seems its a matter of knowing the entire vba coding statements realm

which I don't, to solve any problem. Thanks again for all your help. cr, Kingwood, Tx.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

And it seems its a matter of knowing the entire vba coding statements realm

? I don't think that any of us do that!

Anyway, glad you got something that worked for you.