Compare Columns

CF250 · #1 07-04-2009, 08:00 PM

Hi
My first time here so hi to everyone

I have two columns of adjacent data Call them A & B (although this could differ)

If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged

I wanting to run it from a form to enter the column references using a refedit (because the data as I said is not always in A & B it could be K & L for example) then a command button to run the VBA, .

In other words,

If column B contains anything other than , blank cell "CAT", "DOG" or "ELEPHANT" then the content of the cell in column B should REPLACE the content of the adjacent cell in column A leaving the cell in B blank, otherwise column B remains unchanged.

If using the form or refedit is a problem then a standard macro I could run based on columns A & B would be most gratefull.

Thanks

Any help would be appreciated

venkat1926 · #2 07-04-2009, 09:46 PM

try the macro test
there is an input box which will come up. type the column letter for e.g. B.
the entries cat, dog etc should be exact without any other extra spaces etc.
row 1 has column hadings
the macro is

Code:

Sub test()
Dim rng As Range, c As Range, col As String
col = InputBox("type the column you are considering e.g. B")

Worksheets("sheet1").Activate
Set rng = Range(Range(col & 2), Cells(Rows.Count, col).End(xlUp))
For Each c In rng
'MsgBox c

If c = "" Or c = "cat" Or c = "dog" Or c = "elephant" Then
c.Cut
c.Offset(0, -1).Select
ActiveSheet.Paste
End If
Next c
End Sub

CF250 · #3 07-05-2009, 03:24 AM

Cheers for the reply Venkat1926

sort of there

I have attached a sample worksheet

So it leaves anything that is in column B and is in the or statement in your code alone , but replaces the cell in column A with whatever is in column B

Hope that makes sense

Is there anyway one can

CF250 · #4 07-13-2009, 07:20 AM

Hi
venkat1926 solution sort of works but the wrong way round.
ie it replaces content of column A with the content of column B if it matches the references in the OR statement.

I want it to replace the contents of column A if it does NOT match

Can anyone help ?

Welcome to the Excel Forum

Forum Statistics