Comparing Cells

taha54

Board Regular
Joined
Aug 11, 2006
Messages
103
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

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:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
:LOL:
Hey, I've seen enough of your stuff to be able to admit you can doubt me anytime! :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,113,914
Messages
5,545,015
Members
410,646
Latest member
LegenDSlayeR
Top