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

Try: Like "* *"

--
Jim Rech
Excel MVP
"Donald" wrote in message
news:wQGDc.22019$Lh.4105@okepread01...
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

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


--
Regards,
Tom Ogilvy

"Donald" wrote in message
news:wQGDc.22019$Lh.4105@okepread01...
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

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

--
Donald
"Tom Ogilvy" wrote in message
...
If instr(activecell.text," ") then
msgbox "Check cell contents"
exit sub
end if


--
Regards,
Tom Ogilvy

"Donald" wrote in message
news:wQGDc.22019$Lh.4105@okepread01...
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

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.

--
Regards,
Tom Ogilvy

"Donald" wrote in message
news:E0JDc.22030$Lh.2080@okepread01...
Thanks to both, appears as though both methods work. Would one method have
any advantage over the other?

--
Donald
"Tom Ogilvy" wrote in message
...
If instr(activecell.text," ") then
msgbox "Check cell contents"
exit sub
end if


--
Regards,
Tom Ogilvy

"Donald" wrote in message
news:wQGDc.22019$Lh.4105@okepread01...
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

"Tom Ogilvy" wrote...
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.

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.

--
Regards,
Tom Ogilvy




"Harlan Grove" wrote in message
...
"Tom Ogilvy" wrote...
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" wrote...
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.

--
To top-post is human, to bottom-post and snip is sublime.

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

--
Donald
"Harlan Grove" wrote in message
...
"Tom Ogilvy" wrote...
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.

--
To top-post is human, to bottom-post and snip is sublime.