# Comparing Cells

#### taha54

##### Board Regular
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

Code:
``````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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
Code:
``````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``````

perfect, thanks alot my friend

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

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
ABCDE
1Beforedog1...
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
ABCDE
1Afterdog1...
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:

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

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

Replies
1
Views
118
Replies
13
Views
444
Replies
24
Views
668
Replies
7
Views
170
Replies
13
Views
731

1,217,314
Messages
6,135,805
Members
449,965
Latest member
Ckl43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back