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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

HalfAce

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

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,456
:LOL:
Hey, I've seen enough of your stuff to be able to admit you can doubt me anytime! :LOL:
 

Forum statistics

Threads
1,144,394
Messages
5,724,084
Members
422,536
Latest member
Zeeshan53

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top