Cell Comparison

Posted by XI on August 17, 2000 8:10 AM

The adding row after comparison posted by Henri 2363.html

is similiar to what I want. I want to compare 2 cells then do nothing if the are the same, if they are different add an empty row.

example data:

col1 col2 col3
123 123 1000
456 789 5000
789 0 0

formatted

col1 col2 col3
123 123 1000
456
789 789 5000

etc.

I tried using the code from the previous mentioned post, however I can't get the code to run. It always says that Set is 'invalid outside procedure.'

What do I need to do differently?

Thanks.

Posted by Celia on August 18, 0100 12:58 AM

XI
I'm not sure from your sample data what you want to do - the sample data doesn't match your explanation. Do you mean that for each row, if column A is not the same as column B, then you would like to shift the cells in columns B & C down one row?
Would like to help but please clarify.
Also, please note that I ran the code that you mentioned from the previous post without any error messages.
Celia

Posted by X1 on August 18, 0100 6:56 AM

Celia-

Yes sorry I wasn't very clear. I would like to compare A to B and if not the same then move down B and C. The code I am currently using is:

Sub Compare()

Dim cell As Range
Set cell = Range("A1")
Do Until cell = ""
If cell.Offset(-2, 0) &LT;&GT; cell.Offset(-1, 0) Then cell.EntireRow.Insert
Set cell = cell.Offset(2, 0)
Loop

End Sub

I get a Run Time error '1004';
Application Defined or object-defined error
It then highlights the section of code

If cell.Offset(-2, 0) &LT;&GT; cell.Offset(-1, 0) Then

I understand that the code that I am requesting will be a little different then that posted.

Thank You!

Posted by Celia on August 18, 0100 4:12 PM

X1
Try this :-

Sub Compare()
Dim cell As Range
Set cell = Range("A1")
Do Until cell = ""
If cell &LT;&GT; cell.Offset(0, 1) Then
Range(cell.Offset(0, 1), cell.Offset(0, 2)).Insert Shift:=xlDown
End If
Set cell = cell.Offset(1, 0)
Loop
End Sub

Celia