check cell contents
 

I need some help with a loop I want to check a range of cells and select only
the cells with a valid product in them for example i want to copy ice cream
but ignore any cells that have nothing in them or have the word product in
them I am trying to use the loop below

If Cells(x, 1).Text < "Product" And _
Cells(x, 1).Text < "" Then
d = x - 1

unfortunately it would appear that the text formatting in each cell hase a
bearing for example product will be selected if the font colour is say blue.
is there anyway around this
--
Jack

check cell contents
 

Try using .Value instead of .Text
--
- K Dales


"Jack" wrote:

I need some help with a loop I want to check a range of cells and select only
the cells with a valid product in them for example i want to copy ice cream
but ignore any cells that have nothing in them or have the word product in
them I am trying to use the loop below

If Cells(x, 1).Text < "Product" And _
Cells(x, 1).Text < "" Then
d = x - 1

unfortunately it would appear that the text formatting in each cell hase a
bearing for example product will be selected if the font colour is say blue.
is there anyway around this
--
Jack

check cell contents
 

I have tried value it will not work either the the and in the loop should
have been an or but no matter what I try it still will not work.
--
Jack

"K Dales" wrote:

Try using .Value instead of .Text
--
- K Dales


"Jack" wrote:

I need some help with a loop I want to check a range of cells and select only
the cells with a valid product in them for example i want to copy ice cream
but ignore any cells that have nothing in them or have the word product in
them I am trying to use the loop below

If Cells(x, 1).Text < "Product" And _
Cells(x, 1).Text < "" Then
d = x - 1

unfortunately it would appear that the text formatting in each cell hase a
bearing for example product will be selected if the font colour is say blue.
is there anyway around this
--
Jack

check cell contents
 

The format should not matter. Are the words (e.g. "Product") typed in to the
cells or are they the result of some formula? Regardless, there are a couple
potential problems when trying to test text: first, upper vs. lower case;
e.g. "product" < "Product". Then, sometimes there are spaces typed before
or after the word that are hard to find just by looking, but "Product " <
"Product". These are especially problems if users are typing the values; it
is easy for someone to put a space after the word and not even be aware of it.

This would deal with both of those issues:
If Trim(Ucase(Cells(x,1).Value))<"PRODUCT" Then...

If this does not do it then you may have a problem with your loop. Are you
sure Cells(x,1) is pointing to the cell you think it is? Cells(x,1), unless
you qualify it, will refer to the active worksheet only so if you have
switched to another sheet you may be looking at the wrong cell. You could
but a breakpoint on that line and test what sheet you are on and the address
of the cell to make sure. Also, while at it, test what the value of
Cells(x,1).Value is and also Len(Cells(x,1).Value) to see if there are any
extra spaces or other characters.
--
- K Dales


"Jack" wrote:

I have tried value it will not work either the the and in the loop should
have been an or but no matter what I try it still will not work.
--
Jack

"K Dales" wrote:

Try using .Value instead of .Text
--
- K Dales


"Jack" wrote:

I need some help with a loop I want to check a range of cells and select only
the cells with a valid product in them for example i want to copy ice cream
but ignore any cells that have nothing in them or have the word product in
them I am trying to use the loop below

If Cells(x, 1).Text < "Product" And _
Cells(x, 1).Text < "" Then
d = x - 1

unfortunately it would appear that the text formatting in each cell hase a
bearing for example product will be selected if the font colour is say blue.
is there anyway around this
--
Jack