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

razzandy

Active Member
Joined
Jun 26, 2002
Messages
390
Office Version
  1. 2007
Platform
  1. Windows
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!

Thanks in advance

Cheers

Ryan A UK :rolleyes: :oops:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You don't necessarily need code...

Try:

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

Where A1:A5 is the range of cells to be checked.
 
Upvote 0
Many thanks for your reply Kristy

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.AddItem
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
 
Upvote 0
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.
 
Upvote 0
That works perfect! I've never heard of 'Like' before :biggrin:

Many Thanks
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top