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

[razzandy]

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

 

[Von Pookie]

You don't necessarily need code...

Try:

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

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

 

[razzandy]

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

 

[Von Pookie]

Not fully tested, but try:

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.

 

[razzandy]

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

Many Thanks

 

[Von Pookie]

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