# Code to check if cells in a column containsthe word CALL OFF

#### razzandy

##### Active Member
I’m after some code which checks a cell in a column to see if it contains the words “CALL OFF”. The cell may have somthing like "88/79 CALL OFF"

I’ve no problem doing the loop to check down the column it’s just the looking for the text within test I'm struggling with!

Cheers

Ryan A UK

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
You don't necessarily need code...

Try:

=COUNTIF(A1:A5,"=*CALL OFF*")

Where A1:A5 is the range of cells to be checked.

That’s not what I’m really looking for! I’m after producing reports and making calculations so one way of explaining what I want is:

If the cell contains “CALL OFF” then exclude this row from the report.

Shp = 0
LstRow = 0
For T = 1 To R
If Rng.Cells(T, 12).Text Contains “CALL OFF” Then

ShpGrCom.Column(0, Shp) = Rng.Cells(T, 2).Value
ShpGrCom.Column(1, Shp) = Rng.Cells(T, 3).Value
ShpGrCom.Column(2, Shp) = Rng.Cells(T, 4).Value
ShpGrCom.Column(3, Shp) = Rng.Cells(T, 5).Value

ShpGrCom.Column(4, Shp) = Format(Sheets("Orders").Range("Order").Cells _
(Rng.Cells(T, 2), 9), "dd/mm/yyyy")

ShpGrCom.Column(5, Shp) = Format(Rng.Cells(T, 19).Value, "###0.00")
ShpGrCom.Column(6, Shp) = Format(Rng.Cells(T, 20).Value, "###0.00")
ShpGrCom.Column(7, Shp) = Format(Rng.Cells(T, 18).Value, "dd/mm/yyyy")
LstRow = T
Shp = Shp + 1
End If
Next T

Hope this helps

Cheers

Ryan UK

Not fully tested, but try:

Code:
``If Rng.Cells(T, 12).Value Like "*CALL OFF*" Then``

Another thing to consider would be using a filter to hide all the rows with "call off" and then just work with the visible rows.

I don't know what your data looks like, though--so I'm not sure what would work out for you.

That works perfect! I've never heard of 'Like' before

Many Thanks

I hadn't either until I happened to run across it while poking around the VBA help files one day.

It's the "Like Operator" if you want to look it up

Replies
3
Views
213
Replies
1
Views
215
Replies
4
Views
328
Replies
2
Views
260
Replies
1
Views
199

1,196,230
Messages
6,014,134
Members
441,805
Latest member
rwm

### 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.

### Which adblocker are you using?

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

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