Can someone tell me what's wrong with this code please?

[Guest]

Hi

I have 2 sets of information side by side as I am running a comparrison.

Information 1 is in columns A to D and info 2 is in columns F to I

Info 1 has 867 records with column 2 having slightly more. When I run the code below, everything works fine until it reaches record 867 and then everything below is ignored. How can I ammend the code to keep checking the range beyond it's start point.

Here is the code.

Sub Macro1()
Dim myCell As Range
Dim row As Long

Range("A").Sort Key1:=Range("A1"), order1:=xlAscending, header:=xlNo
Range("F:I").Sort Key1:=Range("F1"), order1:=xlAscending, header:=xlNo

For row = 1 To Application.Count(Range("A:A"))

If Cells(row, 6).Value <> Cells(row, 1).Value And Cells(row, 1).Value < Cells(row, 6).Value Then
Cells(row, 6).Insert Shift:=xlDown
Cells(row, 7).Insert Shift:=xlDown
Cells(row, 8).Insert Shift:=xlDown
Cells(row, 9).Insert Shift:=xlDown
Else
If Cells(row, 1).Value <> Cells(row, 6).Value And Cells(row, 6).Value < Cells(row, 1).Value Then
Cells(row, 1).Insert Shift:=xlDown
Cells(row, 2).Insert Shift:=xlDown
Cells(row, 3).Insert Shift:=xlDown
Cells(row, 4).Insert Shift:=xlDown
End If
End If
Next row
End Sub

Any help with this is greatly appreciated.

Regards

Maclolm Davidson

 

[Guest]

Your code will only take it to the end of column A - you will need to use the column with the most entries too.

 

[Guest]

Work backwards!

For row = Application.Count(Range("A:A")) to 1 step -

HTH

 

[Tom Ogilvy]

Sub Macro1()
Dim myCell As Range
Dim row As Long, i as Long

Range("A").Sort Key1:=Range("A1"), order1:=xlAscending, header:=xlNo
Range("F:I").Sort Key1:=Range("F1"), order1:=xlAscending, header:=xlNo

row= 1
Do until isempty(cells(row,"A")) and isempty(Cells(row,"F"))

If Cells(row, 6).Value <> Cells(row, 1).Value And Cells(row, 1).Value <
Cells(row, 6).Value Then
Cells(row, 6).Insert Shift:=xlDown
Cells(row, 7).Insert Shift:=xlDown
Cells(row, 8).Insert Shift:=xlDown
Cells(row, 9).Insert Shift:=xlDown
Else
If Cells(row, 1).Value <> Cells(row, 6).Value And Cells(row, 6).Value <
Cells(row, 1).Value Then
Cells(row, 1).Insert Shift:=xlDown
Cells(row, 2).Insert Shift:=xlDown
Cells(row, 3).Insert Shift:=xlDown
Cells(row, 4).Insert Shift:=xlDown
End If
End If
row = row + 1
Loop
End Sub

--
Regards,
Tom Ogilvy

Hi

I have 2 sets of information side by side as I am running a comparrison.

Information 1 is in columns A to D and info 2 is in columns F to I

Info 1 has 867 records with column 2 having slightly more. When I run the

code below, everything works fine until it reaches record 867 and then
everything below is ignored. How can I ammend the code to keep checking the
range beyond it's start point.

 

[Guest]

Thanks Lawlera & Tom Ogilv

The code is working fine now

Much appreciated.