Comparing Cells

[taha54]

hi guys,
i have words in cells B , each cell has one word, i am trying to compare the word in Cell B(x) and B(x+1) and see if they are the same, if so, then i want to put "Not Available" in B(x+1)

i want to this as long as there are words in B, i wrote the following code but it doesnt work...any ideas

For k = 1 To Cells(Rows.Count, "B").End(xlUp).Row
comp1 = Cells(k, 1)
comp2 = Cells(k + 1, 1)
If comp1 = comp2 Then
comp2 = "NOT Available"
End If

Next k

thanks alot

 

[HalfAce]

Hello taha54,
There are a couple problems with the posted code, not the least of which is that the
list of words is in column B and the column being looped through is column A.
Try this and see if it's any better.

Dim k As Long, comp1 As Range, comp2 As Range

For k = 1 To Cells(Rows.Count, "B").End(xlUp).Row
  Set comp1 = Cells(k, "B")
  Set comp2 = Cells(k + 1, "B")
  If comp1 = comp2 Then
    comp2.Value = "NOT Available"
  End If

Next k

 

[taha54]

perfect, thanks alot my friend

 

[Richard Schollar]

Hi Taha54

I must admit I don't fully understand your code - If Cells(k,"B") doesn't equal the value in Cells(k+1,"B") then all following cells will be overwritten by "NOT available". Maybe this is what you wanted?

Richard

 

[HalfAce]

If Cells(k,"B") doesn't equal the value in Cells(k+1,"B") then all following cells will be overwritten by "NOT available".

Richard,
Not true... Check it out.

Before running the code:

Book1
	A	B	C	D	E
1	Before	dog1	.	.	.
2	.	dog2	.	.	.
3	.	dog3	.	.	.
4	.	dog4	.	.	.
5	.	dog4	.	.	.
6	.	dog6	.	.	.
7	.	dog7	.	.	.
8	.	dog8	.	.	.
9	.	dog9	.	.	.
10	.	dog9	.	.	.
11	.	dog11	.	.	.
12	.	dog12	.	.	.
13	.	dog13	.	.	.
14	.	dog13	.	.	.
15	.	dog15	.	.	.
Sheet2

After running the code:

Book1
	A	B	C	D	E
1	After	dog1	.	.	.
2	.	dog2	.	.	.
3	.	dog3	.	.	.
4	.	dog4	.	.	.
5	.	NOTAvailable	.	.	.
6	.	dog6	.	.	.
7	.	dog7	.	.	.
8	.	dog8	.	.	.
9	.	dog9	.	.	.
10	.	NOTAvailable	.	.	.
11	.	dog11	.	.	.
12	.	dog12	.	.	.
13	.	dog13	.	.	.
14	.	NOTAvailable	.	.	.
15	.	dog15	.	.	.
Sheet2

What you were thining would be true if we were testing to see
If Cells(k,"B") and Cells(k+1,"B") were not equal, but in
this case it's being tested to see if they are. :wink:

 

[Richard Schollar]

Whoops - got that completely ****-about-face didn't I? You know I checked and re-checked 'cos I couldn't understand what it was doing. Totally missed that. I think I need to have a lie down...

And I should never have doubted you, Halface

 

[HalfAce]

Hey, I've seen enough of your stuff to be able to admit you can doubt me anytime!