Like Operator

[Donald]

I'm trying to stop a procedure if an excel cell contains a space.
Example; cell contains <M T91490>

I've tried an if statement, but it's not returning the result I'm looking
for.

If activecell.value like " " then
msgbox "Check cell contents"
exit sub
end if

I've also tried this;

If activecell.text like " " then
msgbox "Check cell contents"
exit sub
end if

 

[Jim Rech]

Try: Like "* *"

 

[Tom Ogilvy]

If instr(activecell.text," ") then
msgbox "Check cell contents"
exit sub
end if

 

[Donald]

Thanks to both, appears as though both methods work. Would one method have
any advantage over the other?

 

[Tom Ogilvy]

I haven't done any timing tests to see if one is faster than the other, but
I don't think either would have an observable difference.

 

[Harlan Grove]

If instr(activecell.text," ") then
msgbox "Check cell contents"
exit sub
end if

Why .Text rather than .Value? Unexpected things could happen in some Date
and mixed Date/Time number formats. Suppose it's up to the OP whether dates
appearing like '28 Jun 2004' but stored as 38166 would be considered
stopping criteria.

InStr(x, " ") is likely to be faster than x Like "* *", but a single
instance of either would take negligible time.

 

[Tom Ogilvy]

I assume the OP can determine what is appropriate to his or her situation.

ActiveCell.Text appeared most appropriate to me in this instance. Dates
weren't an issue in the question and it wasn't offered as a turnkey solution
to all such tests. As long as we are going to imagine any conceivable
situation, if in regional settings the user set MM dd, yyyy as the short
date format, then using value would be problematic as well:

From the immediate window:
? activeCell.Value
Jun 24, 2004
? Instr(activecell.Value," ")
4

So value2 would need to be used in that case.

Thanks for your interest.

 

[Harlan Grove]

I assume the OP can determine what is appropriate to his or her situation.

They always have to anyway.

ActiveCell.Text appeared most appropriate to me in this instance. Dates
weren't an issue in the question and it wasn't offered as a turnkey solution
to all such tests. As long as we are going to imagine any conceivable
situation, if in regional settings the user set MM dd, yyyy as the short
date format, then using value would be problematic as well: ...
So value2 would need to be used in that case.

In the sense that the OP's single example, "cell contains <M T91490>" made it
appear the cells would contain text, then there would have been little
difference between .Text and .Value. I fully agree that .Value2 would have made
more sense than your original suggestion of .Text.

 

[Donald]

Thanks to all. In my particular scenario, the cell will/should always
contain an alphameric entry (text if you will).