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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
731
This code works perfectly every time. But the value of x must match EXACTLY the contents of the cell value
Code:
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
 

Attachments

  • cellvalue.JPG
    cellvalue.JPG
    11.1 KB · Views: 8
  • cellvaluewithin.JPG
    cellvaluewithin.JPG
    11.3 KB · Views: 8

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,420
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
If InStr(1, Cells(i, 4), x, vbTextCompare) > 0 Then
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,386
Office Version
  1. 365
Platform
  1. Windows
No data to test with but does this fix it?

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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
731
How about
VBA Code:
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 Lookin:(xlPartial) 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

Well-known Member
Joined
Oct 21, 2006
Messages
731
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.
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,420
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,386
Office Version
  1. 365
Platform
  1. Windows
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. (y)
 

Forum statistics

Threads
1,186,123
Messages
5,955,973
Members
438,229
Latest member
paulmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top